Read & Filter Data

Query, filter, sort, paginate, and aggregate your Totalum data with the SDK.

#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

javascript

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

javascript

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

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

javascript

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.

javascript

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.

javascript

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.

javascript

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)

javascript

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.

javascript

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

javascript

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

javascript

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

javascript

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

#Filter with `regex`

javascript

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

javascript

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

javascript

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

javascript

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

javascript

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

javascript

// 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'.

javascript

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

#Count children

javascript

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

javascript

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

javascript

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

javascript

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

javascript

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

javascript

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

javascript

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

javascript

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

javascript

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

#How Filter Works

This section describes the filter format used with totalumClient.crud.query().

#Basic structure

javascript

const result = await totalumClient.crud.query('your_table', {
    _filter: {
        property_name: "value"
    },
    _sort: { property_name: "asc" },   // "asc" or "desc"
    _limit: 50,
    _offset: 0,
});

#How pagination works

Use _limit to set the number of items per page and _offset to skip items. Default limit is 100.

javascript

// Page 1: items 0-49
{ _limit: 50, _offset: 0 }

// Page 2: items 50-99
{ _limit: 50, _offset: 50 }

#How sorting works

Use _sort with "asc" (ascending) or "desc" (descending):

javascript

{ _sort: { name: "asc" } }
{ _sort: { created_at: "desc" } }

#Filter operators

#Filter by exact value (string, number, or date)

javascript

{ _filter: { name: "John" } }
{ _filter: { age: 25 } }
{ _filter: { date: "2024-01-01T00:00:00.000Z" } }

#Filter by partial string (regex)

javascript

{ _filter: { name: { regex: "john", options: "i" } } }
// options: "i" = case insensitive

#Filter by number range

javascript

{ _filter: { price: { gte: 1, lte: 10 } } }

#Filter by date range

javascript

{ _filter: { date: { gte: "2024-01-01T00:00:00.000Z", lte: "2024-01-10T00:00:00.000Z" } } }

#Filter by not equal to

javascript

{ _filter: { status: { ne: "inactive" } } }

#Filter by list (in / not in)

javascript

{ _filter: { status: { in: ["active", "pending"] } } }
{ _filter: { status: { nin: ["deleted"] } } }

#Filter by contains, startsWith, endsWith

javascript

{ _filter: { name: { contains: "john" } } }
{ _filter: { name: { startsWith: "jo" } } }
{ _filter: { email: { endsWith: "@gmail.com" } } }

#Filter by a table relation (One to Many)

Use Case:

Imagine you have 2 tables, client and order. Each client can have multiple orders. You want to get all the orders of a specific client.

javascript

const result = await totalumClient.crud.query('order', {
    _filter: { client: "the_client_id" }
});

#Combining filters

#AND condition (all must match)

All properties at the top level of _filter are combined with AND:

javascript

{
    _filter: {
        status: "active",
        country: "Spain",
        age: { gte: 18 },
        name: { regex: "john", options: "i" },
        role: { ne: "admin" }
    }
}

#OR condition (at least one must match)

Use _or to combine conditions with OR:

javascript

{
    _filter: {
        _or: [
            { status: "active" },
            { status: "pending" },
            { age: { gte: 65 } }
        ]
    }
}

#AND + OR combined

javascript

{
    _filter: {
        country: "Spain",               // AND this
        _or: [
            { status: "active" },        // OR this
            { status: "pending" }         // OR this
        ]
    }
}

This filter will return the items where country is "Spain" AND status is "active" or "pending".

#Full example

javascript

const result = await totalumClient.crud.query('product', {
    _filter: {
        category: "electronics",
        price: { gte: 10, lte: 500 },
        name: { regex: "phone", options: "i" },
    },
    _sort: { price: "asc" },
    _limit: 20,
    _offset: 0,
});
const items = result.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.

javascript

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:

json

"_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.

javascript

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

javascript

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;

Read & Filter Data

#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

#How Filter Works

#Basic structure

#How pagination works

#How sorting works

#Filter operators

#Filter by exact value (string, number, or date)

#Filter by partial string (regex)

#Filter by number range

#Filter by date range

#Filter by not equal to

#Filter by list (in / not in)

#Filter by contains, startsWith, endsWith

#Filter by a table relation (One to Many)

#Combining filters

#AND condition (all must match)

#OR condition (at least one must match)

#AND + OR combined

#Full example

#Get the historic updates of a record by its ID

#Filter using your custom mongoDb aggregation query

#Filter with `in` operator

#Filter with `regex`

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