Read and Filter Data

📚 Setup Required: For installation and usage of the Totalum SDK or API, see the Installation Guide.

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.getRecordById(tableElementName, your_item_id);
const item = result.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.getRecordById(tableElementName, clientId);
const client = result.data;

Query (Recommended)

totalumClient.crud.query() is the recommended method for reading data.

Note: If you get an error like query is not a function, update the SDK: npm install totalum-api-sdk@latest

If you are using deprecated methods like getRecords, getNestedData, getManyToManyReferencesRecords, or nestedFilter, see the Deprecated Get Methods page for migration guide.

It supports:

Nested relations (oneToMany, manyToOne, manyToMany) at any depth
Filtering with exact match, comparison operators, regex, _or, in, contains, startsWith, endsWith
Sorting, pagination (_limit, _offset)
Parent filtering by children (_has)
Child counts (_count)
Field selection (_select, _omit)
Aggregations (_aggregate, _groupBy)

Get items (simple)

Get items from a table without any filters (default limit: 50 items).

const result = await totalumClient.crud.query('client');
const clients = result.data;

Get items with nested relations

Use Case:

Imagine you have 3 tables: client, order and product. You want to get clients with all their orders and each order's products.

const result = await totalumClient.crud.query('client', {
    order: {
        product: true
    }
});

const clients = result.data;
/*
Result structure:
[
    {
        _id: 'client_id_1',
        name: 'Client 1',
        order: [
            {
                _id: 'order_id_1',
                date: '2023-01-01',
                product: [
                    { _id: 'product_id_1', name: 'Product 1' },
                    { _id: 'product_id_2', name: 'Product 2' }
                ]
            },
        ]
    },
    // more clients...
]
*/

Important: The property names in the query (order, product) must be the property names (field names) defined in your table, not the table names.

You can use true as a shorthand for {} — both expand the relation with default settings.

Get items with manyToOne relations

Use Case:

Imagine you have a task table with a employee field (manyToOne) and employee has a company field (manyToOne). You want to get tasks with the full employee and company data.

const result = await totalumClient.crud.query('task', {
    employee: {
        company: true
    }
});

const tasks = result.data;
/*
Result structure (manyToOne returns a single object, not an array):
[
    {
        _id: 'task_id_1',
        title: 'Fix bug',
        employee: {
            _id: 'emp_id_1',
            name: 'Alice',
            company: {
                _id: 'company_id_1',
                name: 'Acme Corp'
            }
        }
    },
    // more tasks...
]
*/

Get items with manyToMany relations

Use Case:

Imagine employee has a manyToMany relation project. You want to get all employees with their projects.

const result = await totalumClient.crud.query('employee', {
    project: true
});

const employees = result.data;
/*
[
    {
        _id: 'emp_id_1',
        name: 'Alice',
        project: [
            { _id: 'proj_id_1', name: 'Website Redesign' },
            { _id: 'proj_id_2', name: 'Mobile App' }
        ]
    },
    // more employees...
]
*/

Filter items (exact match)

const result = await totalumClient.crud.query('client', {
    _filter: { name: 'John' }
});
const clients = result.data;

Filter with comparison operators

Supported operators: gte (>=), lte (<=), ne (!=), in, nin, contains, startsWith, endsWith, regex.

// Get clients older than 18 with birthday after 2000
const result = await totalumClient.crud.query('client', {
    _filter: {
        age: { gte: 18 },
        birthday: { gte: new Date('2000-01-01') }
    }
});

Filter with OR conditions

// Get clients named 'John' OR aged 18+
const result = await totalumClient.crud.query('client', {
    _filter: {
        _or: [
            { name: 'John' },
            { age: { gte: 18 } }
        ]
    }
});

Filter with AND + OR combined

// Get clients (named 'John' OR aged 18+) AND address contains 'street'
const result = await totalumClient.crud.query('client', {
    _filter: {
        _or: [
            { name: 'John' },
            { age: { gte: 18 } }
        ],
        address: { contains: 'street' }
    }
});

Filter with `in` operator

// Get clients with status 'active' or 'pending'
const result = await totalumClient.crud.query('client', {
    _filter: {
        status: { in: ['active', 'pending'] }
    }
});

Filter with `regex`

// Get clients whose name contains 'john' (case insensitive)
const result = await totalumClient.crud.query('client', {
    _filter: {
        name: { regex: 'john', options: 'i' }
    }
});

Filter with `contains`, `startsWith`, `endsWith`

// These are shortcuts for common regex patterns
const result = await totalumClient.crud.query('client', {
    _filter: {
        name: { startsWith: 'Jo' },      // names starting with "Jo"
        email: { endsWith: '@gmail.com' }, // gmail emails
        address: { contains: 'street' }    // address contains "street"
    }
});

Sort results

// Sort by name ascending
const result = await totalumClient.crud.query('client', {
    _sort: { name: 'asc' }
});

// Sort by age descending
const result2 = await totalumClient.crud.query('client', {
    _sort: { age: 'desc' }
});

// You can also use 1 (asc) and -1 (desc)
const result3 = await totalumClient.crud.query('client', {
    _sort: { name: 1, age: -1 }
});

Pagination (limit and offset)

// Get first 10 clients
const result = await totalumClient.crud.query('client', {
    _limit: 10
});

// Get clients 11-20 (skip first 10)
const result2 = await totalumClient.crud.query('client', {
    _limit: 10,
    _offset: 10
});

Limit children

// Get companies with only the first 5 employees
const result = await totalumClient.crud.query('company', {
    employee: {
        _limit: 5,
        _sort: { name: 'asc' }
    }
});

Filter on children

You can filter children and also use _has to filter parents based on their children.

// Get companies that have at least one active employee
const result = await totalumClient.crud.query('company', {
    employee: {
        _filter: { status: 'active' },
        _has: true   // only return companies that have matching employees
    }
});

_has supports: true (same as 'some'), 'some', 'none', 'every'.

// Get companies with NO employees
const result = await totalumClient.crud.query('company', {
    employee: {
        _has: 'none'
    }
});

Count children

// Get companies with the count of their employees
const result = await totalumClient.crud.query('company', {
    employee: {
        _count: true
    }
});

const companies = result.data;
/*
[
    {
        _id: 'company_id_1',
        name: 'Acme Corp',
        employee: [ ... ],
        _count: { employee: 15 }
    }
]
*/

Count total (root level)

// Get total count of matching records (useful for pagination)
const result = await totalumClient.crud.query('client', {
    _filter: { status: 'active' },
    _count: true,
    _limit: 10
});

const clients = result.data;
// Each record will have: _count: { _total: 150 }
// So you know there are 150 total active clients, but only 10 returned

Select specific fields

// Only return name and email fields
const result = await totalumClient.crud.query('client', {
    _select: { name: true, email: true }
});

// Result: [{ _id: '...', name: 'John', email: 'john@...' }]
// Other fields like age, address, etc. are NOT returned

Omit specific fields

// Return all fields EXCEPT password and internal_notes
const result = await totalumClient.crud.query('client', {
    _omit: { password: true, internal_notes: true }
});

Note: _select and _omit cannot be used together at the same level.

Hide children from response (_include: false)

Use _include: false when you want to use _has or _count on children but don't want the actual child records in the response.

// Get companies that have employees, with employee count, but don't include employee data
const result = await totalumClient.crud.query('company', {
    employee: {
        _has: true,
        _count: true,
        _include: false  // don't include employee array in response
    }
});

// Result: [{ _id: '...', name: 'Acme', _count: { employee: 15 } }]
// No 'employee' array in the response

Aggregations

Aggregate data with _sum, _avg, _min, _max, _count.

// Get total and average amount of all invoices
const result = await totalumClient.crud.query('invoice', {
    _aggregate: {
        _sum: { amount: true },
        _avg: { amount: true },
        _count: true
    }
});

/*
Result:
[
    {
        _aggregate: {
            _sum: { amount: 50000 },
            _avg: { amount: 250 },
            _count: 200
        }
    }
]
*/

Aggregations with groupBy

// Get total invoice amount grouped by status
const result = await totalumClient.crud.query('invoice', {
    _filter: { date: { gte: new Date('2024-01-01') } },
    _aggregate: {
        _sum: { amount: true },
        _count: true
    },
    _groupBy: 'status'
});

/*
Result:
[
    { _group: { status: 'paid' }, _aggregate: { _sum: { amount: 35000 }, _count: 150 } },
    { _group: { status: 'pending' }, _aggregate: { _sum: { amount: 15000 }, _count: 50 } }
]
*/

Child aggregations

// Get companies with total salary of their employees
const result = await totalumClient.crud.query('company', {
    employee: {
        _aggregate: { _sum: { salary: true }, _avg: { salary: true } },
        _include: false
    }
});

/*
Result:
[
    {
        _id: 'company_id_1',
        name: 'Acme Corp',
        _aggregate: {
            employee: { _sum: { salary: 500000 }, _avg: { salary: 50000 } }
        }
    }
]
*/

Full example: combining multiple features

// Get companies in Spain with active employees, count tasks per employee, sort by name
const result = await totalumClient.crud.query('company', {
    _filter: { country: 'Spain' },
    _sort: { name: 'asc' },
    _limit: 10,
    _count: true,
    employee: {
        _filter: { status: { in: ['active', 'probation'] } },
        _has: true,
        _count: true,
        _sort: { name: 'asc' },
        _limit: 20,
        task: {
            _sort: { createdAt: 'desc' },
            _limit: 5
        }
    }
});

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;

Filter using your custom mongoDb aggregation query

Use Case:

If you need to do a super complex 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`.

Important Information:

For match by Id (ObjectId), as the mongoDb query is a string, you need to put: ObjectId('your_id') in the query string, instead of just 'your_id'.
For match by date, you need to put the date in the format: Date('your_date') in the query string, instead of just 'your_date'. Ideally provide a iso date string like '2021-01-01T00:00:00.000Z' for avoid time zone issues.

// 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;

Deprecated Methods

The following methods are deprecated. See the full documentation and migration guide in the Deprecated Get Methods page.

Read and Filter Data

Get item by id​

Query (Recommended)​

Get items (simple)​

Get items with nested relations​

Get items with manyToOne relations​

Get items with manyToMany relations​

Filter items (exact match)​

Filter with comparison operators​

Filter with OR conditions​

Filter with AND + OR combined​

Filter with in operator​

Filter with regex​

Filter with contains, startsWith, endsWith​

Sort results​

Pagination (limit and offset)​

Limit children​

Filter on children​

Count children​

Count total (root level)​

Select specific fields​

Omit specific fields​

Hide children from response (_include: false)​

Aggregations​

Aggregations with groupBy​

Child aggregations​

Full example: combining multiple features​

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

Filter using your custom mongoDb aggregation query​

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.​

Important Information:​

Deprecated Methods​

Get item by id

Query (Recommended)

Get items (simple)

Get items with nested relations

Get items with manyToOne relations

Get items with manyToMany relations

Filter items (exact match)

Filter with comparison operators

Filter with OR conditions

Filter with AND + OR combined

Filter with `in` operator

Filter with `regex`

Filter with `contains`, `startsWith`, `endsWith`

Sort results

Pagination (limit and offset)

Limit children

Filter on children

Count children

Count total (root level)

Select specific fields

Omit specific fields

Hide children from response (_include: false)

Aggregations

Aggregations with groupBy

Child aggregations

Full example: combining multiple features

Get the historic updates of a record by its ID.

Filter using your custom mongoDb aggregation query

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`.

Important Information:

Deprecated Methods