Saltar al contenido principal

Leer y Filtrar Datos

What happens if you are not programming in javascript?​

If you are not programming in javascript, you can use the api directly, see TOTALUM API DOCUMENTATION

If you are programming in javascript, you can use the totalumSdk​

Note: If you use totalumSdk inside a totalum plugin, you don't need to authenticate, you can start using totalum sdk functions like this: modules.totalumSdk.crud.getItemById(); etc...

Si tienes dudas de como instalar y usar TotalumSdk mira la documentación: Instalación SDK de Totalum, y Uso del SDK

Get item by id​

Use Case:

Get only one item by id from your element table


// get item by id from your_element_table_name
const tableElementName = 'your_element_table_name'; // replace 'your_element_table_name' with the name of your element table
let your_item_id = 'your_item_id'; // replace 'your_item_id' with the id of the item object
const result = await totalumClient.crud.getItemById(tableElementName, your_item_id);
const item = result.data.data;

example:

Imagine you have a table named client, and this table has some items inside. You want to get a client by id, so you can do this:


const tableElementName = 'client';
let clientId = 'the_client_id'; // replace 'the_client_id' with the id of the item object
const result = await totalumClient.crud.getItemById(tableElementName, clientId);
const item = result.data.data;

Get items​

Use Case:

Get items from your element table without filters and pagination and sort (by default 50 items per page)

Warning: if have more than 50 items, you will need to use pagination to get all the item. (see Filter data using totalum section of this page)


// get items from your_element_table_name (for default 50 items per page)
const tableElementName = 'your_element_table_name'; // replace 'your_element_table_name' with the name of your element table
const result = await totalumClient.crud.getItems(tableElementName, {});
const items = result.data.data;

example:

Imagine you have a table named client, and this table has some items inside. You want to get multiple clients, so you can do this:


const tableElementName = 'client';
const result = await totalumClient.crud.getItems(tableElementName, {});
const items = result.data.data;

Get the historic updates of a record by its ID.​

Use Case:

You can get all changes that have been made to a record.


const result = await totalumClient.crud.getHistoricRecordUpdatesById(yourRecordId); // replace yourRecordId with the id of the record

const updates = result.data.data;

Get nested items​

Get multiple nested items in one query

Use Case:

Imagine you have 3 tables, client, order and product, and you want to get some clients with all orders and the products of the orders


const nestedQuery: NestedQuery = {
    client: {
        order: {
            product: {}
        },
    }
}

const result = await totalumClient.crud.getNestedData(nestedQuery);

For default, the nested query will return 100 for each table. If you want to get all the items, you will need to use pagination and do multiple queries. In the next example, you will see how to use pagination to get items.

Get nested items and filter​

This is the same as the previous example, but now you want to filter the results


// As before you have client, order, and product tables, but you now want to only get the client with name 'Jhon' and limit to 10 results

const nestedQuery: NestedQuery = {
    client: {
        tableFilter: {
            filter: [{
              name: 'Jhon'
            }],
            sort: {
              name: 1
            },
            pagination: {
              limit: 10,
              page: 0,
            }
        },
        order: {
            product: {}
        },
    }
}

const result = await totalumClient.crud.getNestedData(nestedQuery);

More information of how to use filters, sort, and pagination in the section Filter data using totalum

Nested Filter​

get table items by Filtering others related tables. (like a join filter in sql)

The difference between getNestedData and nestedFilter is that nestedFilter only gets the items of the table that you specify in the tableNameToGet parameter that matches the nested filter.

Use Case:

Imagine you have 3 tables, client, order and product, and you want to get the clients that have an order with state equal to completed and that order_date is from 2021-01-01 to 2021-01-31, and that order must have a product with a product with the name Cocacola. And you to get only the first 50 clients that match the filter.


// you can filter in all tables for all the properties of the table if you want, in this example we are filtering only the order and product tables
const nestedFilter = {
    client: {
        order: {
            tableFilter: [
                {
                    state: 'completed'
                },
                {
                    order_date: {
                        gte: new Date('2021-01-01'),
                    }
                },
                {
                    order_date: {
                        lte: new Date('2021-01-31')
                    }
                }
            ],
            product: {
                tableFilter: [ 
                    {
                        name: 'Cocacola'
                    }
                ]
            }
        },
    }
}

const filterOptions = {
      pagination: {
        limit: 60,
        page: 0
      },
      sort: {
        // you can sort by any field of contact, for example, sort by email
        email: -1
      }
};

// the table name to get the data that matches the filter
const tableNameToGet = 'client';

const result = await totalumClient.filter.nestedFilter(nestedFilter, tableNameToGet, filterOptions);

const clients = result.data.data;

Another use case:

You can also get the tables that are not in top.

Imagine you have 3 tables, client, order and product, and you want to get the products that have an order with state equal to completed, and that order must have a client with the name Jhon. And you to get only the first 50 products that match the filter.


const nestedFilter = {
    client: {
        tableFilter: [
            {
                name: 'Jhon'
            }
        ],
        order: {
            tableFilter: [
                {
                    state: 'completed'
                }
            ],
            product: {}
        },
    }
}

const filterOptions = {
      pagination: {
        limit: 60,
        page: 0
      },
      sort: {
        // you can sort by any field of product
        name: -1
      }
};

// the table name to get the data that matches the filter
const tableNameToGet = 'product';

const result = await totalumClient.filter.nestedFilter(nestedFilter, tableNameToGet, filterOptions);

const products = result.data.data;

You can do the same approach to get the orders.

get many to many references items​

Use Case:

THIS ONLY WORKS FOR MANY TO MANY RELATIONSHIPS, IF YOU WANT TO GET ONE TO MANY or MANY TO ONE REFERENCES ITEMS, USE THE METHOD getItems INSTEAD.

Get many to many references items from your element table without filters and pagination and sort (by default 50 items per page)


const tableElementName = 'your_element_table_name'; // replace 'your_element_table_name' with the name of your element table
let your_item_id = 'your_item_id'; // replace 'your_item_id' with the id of the item
const propertyName = 'your_property_name'; // replace 'your_property_name' with the name of the property that have a many to many relationship

// the query is optional, you can use it to filter and sort the results
const query = {
    filter: [
        {
            'your_property_name': 'value' // add your custom filters here
        },
    ],
    sort:{
        'your_property_name': 1 // 1 for asc, -1 for desc
    },
    pagination: {
        limit: 50,
        page: 0,
    }
};
const result = await totalumClient.crud.getManyToManyReferencesItems(tableElementName, your_item_id, propertyName, query);

More information of how to use filters, sort, and pagination in the section Filter data using totalum

example:

Imagine you have a table named client, and this table has a property (field) named books that this property has a many to many relationship with the table book. (so a client can have many books, and a book can have many clients).

You want to get all the books of a client, so you can do this:

Warning: if have more than 50 books, you will need to use pagination to get all the books. (see example2)


const tableElementName = 'client';
let clientId = 'the_client_id'; // replace 'the_client_id' with the id of the item object
const propertyName = 'books';
const result = await totalumClient.crud.getManyToManyReferencesItems(tableElementName, clientId, propertyName, {});

example2:

The same case as before but now you want to get all the books of a client that have the word 'harry' in the title property of book, so you can do this:


const tableElementName = 'client';
let clientId = 'the_client_id'; // replace 'the_client_id' with the id of the item object
const propertyName = 'books';
const query = {
    filter: [
        {
            'title': {regex: 'harry', options: 'i'} // it matches a value using a regex query and options: i for case insensitive (ignore if it is uppercase or lowercase)
        },
    ],
    sort:{
        'title': 1 // 1 for asc, -1 for desc
    },
    pagination: {
        limit: 50,
        page: 0,
    }
};
const result = await totalumClient.crud.getManyToManyReferencesItems(tableElementName, clientId, propertyName, query);

Filter data using totalum AND filter​

Use Case:

Filter, sort and use pagination of items of a table. You can filter for all items properties and also for references (all conditions must be true)


const tableElementName = 'your_element_table_name'; // replace 'your_element_table_name' with the name of your element table

// get items from your_element_table_name (for default 50 items per page) applying a filter AND query (all conditions must be true)
const filter: FilterSearchQueryI = {
    filter: [
        {
            'your_property_name': 'value' // it matches the value exactly
        },
        {
            'your_other_property_name': {regex: 'your regex query', options: 'i'} // it matches a value using a regex query and options: i for case insensitive (ignore if it is uppercase or lowercase)
        },
        // PD: gte and lte operators are only allowed for properties of type date or number
        {
            'your_other_property_name': {gte: new Date('your date')} // it matches a value greater than or equal to the date
        },
        {
            'your_other_property_name': {lte: new Date('your date')} // it matches a value less than or equal to the date
        },
        {
            'your_other_property_name': {gte: 10} // it matches a value greater than or equal to 10
        },
        {
            'your_other_property_name': {lte: 100} // it matches a value less than or equal to 100
        }
    ],
    sort:{
        'your_property_name': 1 // 1 for asc, -1 for desc
    },
    pagination: {
        limit: 50,
        page: 0,
    }
};

const result = await totalumClient.crud.getItems(tableElementName, filter);
const items = result.data.data;

More information of how to use filters, sort, and pagination in the section Filter data using totalum

example:

Imagine you have a table named client, with properties name (text), age (number), birthday (date), and address (text).

You want to get all the clients that have the name 'John', that are older than 18 years old, that have a birthday greater than or equal to 01/01/2000, and that have an address that contains the word 'street' and sort results per name, so you can do this:


const tableElementName = 'client';
const filter: FilterSearchQueryI = {
    filter: [
        {
            'name': 'John' // it matches the value exactly
        },
        {
            'age': {gte: 18} // it matches a value greater than or equal to 18
        },
        {
            'birthday': {gte: new Date('01/01/2000')} // it matches a value greater than or equal to 01/01/2000
        },
        {
            'address': {regex: 'street', options: 'i'} // it matches a value using a regex query and options: i for case insensitive (ignore if it is uppercase or lowercase)
        },
    ],
    sort:{
        'name': 1 // 1 for asc, -1 for desc
    },
    pagination: {
        limit: 50,
        page: 0,
    }
};

const result = await totalumClient.crud.getItems(tableElementName, filter);
const items = result.data.data;

More information of how to use filters, sort, and pagination in the section Filter data using totalum

Filter data using totalum OR filter​

Use Case:

As the previous example, but now you want to Apply an OR filter instead of an AND filter. (at least one condition must be true)


// get items from your_element_table_name (for default 50 items per page) applying a filter OR query (at least one condition must be true)
const filter: FilterSearchQueryI = {
    filter: [
        {
            or: [
                {
                    'your_property_name': 'value' // it matches the value exactly
                },
                {
                    'your_other_property_name': {regex: 'your regex query', options: 'i'} // it matches a value using a regex query and options: i for case insensitive (ignore if it is uppercase or lowercase)
                },
                {
                    'your_other_property_name': {gte: new Date('your date')} // it matches a value greater than or equal to the date
                },
            ]
        }
    ],
    sort: {
            'your_property_name': 1 // 1 for asc, -1 for desc
    },
    pagination: {
        limit: 50,
        page: 0,
    }
};

const result = await totalumClient.crud.getItems(tableElementName, filter);
const items = result.data.data;

More information of how to use filters, sort, and pagination in the section Filter data using totalum

example:

Imagine you have a table named client, with properties name (text), age (number), birthday (date), and address (text).

You want to get all the clients that have the name 'John', or that are older than 18 years old, or that have a birthday greater than or equal to 01/01/2000, or that have an address that contains the word 'street' and sort results per name, so you can do this:


const tableElementName = 'client';
const filter: FilterSearchQueryI = {
    filter: [
        {
            or: [
                {
                    'name': 'John' // it matches the value exactly
                },
                {
                    'age': {gte: 18} // it matches a value greater than or equal to 18
                },
                {
                    'birthday': {gte: new Date('01/01/2000')} // it matches a value greater than or equal to 01/01/2000
                },
                {
                    'address': {regex: 'street', options: 'i'} // it matches a value using a regex query and options: i for case insensitive (ignore if it is uppercase or lowercase)
                },
            ]
        }
    ],
    sort:{
        'name': 1 // 1 for asc, -1 for desc
    },
    pagination: {
        limit: 50,
        page: 0,
    }
};

const result = await totalumClient.crud.getItems(tableElementName, filter);
const items = result.data.data;

Filter data using totalum AND and OR filter​

Use Case:

As the previous example, but now you want to Apply an AND and OR filter instead of an AND filter. 


// get items from your_element_table_name (for default 50 items per page) applying a filter OR and AND
const filter: FilterSearchQueryI = {
    filter: [
        {
            or: [
                {
                    'your_property_name_in_or': 'value' // it matches the value exactly
                },
                {
                    'your_other_property_name_in_or': {regex: 'your regex query', options: 'i'} // it matches a value using a regex query and options: i for case insensitive (ignore if it is uppercase or lowercase)
                },
            ],
        },
        {
            'your_other_property_name': 'value' // it matches the value exactly
        }
        
    ],
    sort: {
        'your_property_name': 1 // 1 for asc, -1 for desc
    },
    pagination: {
        limit: 50,
        page: 0,
    }
};

const result = await totalumClient.crud.getItems(tableElementName, filter);
const items = result.data.data;

example:

Imagine you have a table named client, with properties name (text), age (number), birthday (date), and address (text).

You want to get all the clients that have the name 'John', or that are older than 18 years old, or that have a birthday greater than or equal to 01/01/2000, and that have an address that contains the word 'street' and sort results per name, so you can do this:


const tableElementName = 'client';
const filter: FilterSearchQueryI = {
    filter: [
        {
            or: [
                {
                    'name': 'John' // it matches the value exactly
                },
                {
                    'age': {gte: 18} // it matches a value greater than or equal to 18
                },
                {
                    'birthday': {gte: new Date('01/01/2000')} // it matches a value greater than or equal to 01/01/2000
                },
            ]
        },
        {
            'address': {regex: 'street', options: 'i'} // it matches a value using a regex query and options: i for case insensitive (ignore if it is uppercase or lowercase)
        },
    ],
    sort:{
        'name': 1 // 1 for asc, -1 for desc
    },
    pagination: {
        limit: 50,
        page: 0,
    }
};

const result = await totalumClient.crud.getItems(tableElementName, filter);
const items = result.data.data;

Get all references of an item (One to Many)​

Use Case:

Get all the references of an item (one to many relationship)


const tableElementName = 'your_element_table_name'; // replace 'your_element_table_name' with the name of your element table
const query = {
    filter: [
        {
            'your_property_name': 'the_item_id'
        },
    ],
    // pagination and sort are optional
    sort:{
        'your_property_name': 1 // 1 for asc, -1 for desc
    },
    pagination: {
        limit: 50,
        page: 0,
    }
};

const result = await totalumClient.crud.getItems(tableElementName, query);

examples:

Imagine you have a table named client, and other table named book.

  • The table client has a property name (text), surname (text)
  • The table book has a property title (text), client (many to one relationship with the table client)

You want to get all the books of a specific client, so you can do this:


const tableElementName = 'book';
const query = {
    filter: [
        {
            'client': 'the_client_id'
        },
    ],
    // sort and pagination are optional
    sort:{
        'title': 1 // 1 for asc, -1 for desc
    },
    pagination: {
        limit: 50,
        page: 0,
    }
};

const result = await totalumClient.crud.getItems(tableElementName, query);

Filter using your custom mongoDb aggregation query​

Use Case:

If you need to do a custom query that is not supported by the previous methods, you can use this method to do a custom mongoDb aggregation query. This method is very powerful, you can do any query you want, but you need to know how to write mongoDb aggregation queries. See the documentation of mongoDb aggregation queries here: https://docs.mongodb.com/manual/aggregation/.

Aggregation queries are very powerful for do custom complex queries like joins, group by, union, etc. (the same power as sql queries)

Note:

In Totalum mongoDb Database the tables and items has the following structure:


"_id": 2342342342342,
// here goes all the properties of the item with the custom names and values that you have defined
"property_name": "value",
"property_name2": "value2",
"property_name2": "value2"
//etc...
"createdAt": "2021-01-01T00:00:00.000Z",
"updatedAt": "2021-01-01T00:00:00.000Z"

Each table in Totalum is a mongoDb collection (adding data_ prefix), and each record in the table is a document in the collection.​

So for example, if you have a table named product, in the mongoDb database the collection will be named data_product.​


// filter results from your_element_table_name applying a filter query (a custom mongodb aggregation query)
const customMongoDbAggregationQueryInString = `
  
  your custom mongo aggregation query in string, for more info:
    https://docs.mongodb.com/manual/aggregation/

    or ask to chatgpt, he is very good writing mongo aggregation queries ;)

`;

const result = await totalumClient.filter.runCustomMongoAggregationQuery(tableElementName, customMongoDbAggregationQueryInString);

example

Imagine you have a table named product, with properties name (text), price (number), provider (many to one relation with relationship with the table provider).

And you want to get all the products that have a price greater than 10, and that have a provider that have the name 'John', and also return all products with the full provider autofill, so you can do this:


const tableElementName = 'product';
const customMongoDbAggregationQueryInString = `
    [
        {
            $match: {
                "price": {$gte: 10}
            }
        },
          // Join the data_product with data_provider using provider as the linking _id
        {
            $lookup: {
                from: "data_provider", // we add the prefix "data_" to the table name
                localField: "provider",
                foreignField: "_id",
                as: "provider"  // Now storing the result directly in the "provider" field
            }
        },
        {
            $match: {
                "provider.name": "John"
            }
        },
        // Simplify the provider to be an object instead of an array
        {
            $addFields: {
              "provider": {
                $arrayElemAt: ["$provider", 0]
              }
            }
        }
    ]
`;

const result = await totalumClient.filter.runCustomMongoAggregationQuery(tableElementName, customMongoDbAggregationQueryInString);
const items = result.data.data;