These parameters are available for general search queries:

name	description	default
limit	number of records to fetch	10
offset	offset	0
page	which page	1
page_size	size of page	10
no_filtered_count	do not fetch the number of total records filtered 1 (don't fetch) / 0 (do fetch) - this will not work if total_count is turned off as it's needed for safety at that point	0
no_total_count	do not fetch the total records in the entity 1 (don't fetch) / 0 (do fetch)	0
no_counts	set no_filtered_count and no_total_count to 1	0
conds	search conditions ("where")
attrs	search attributes (joins, order by, group by etc)
context	search Query-Context

TOC:

• Returned values
  • Estimated count values
• Query-Context
• General Query /data/$model/$entity/query
  • Conds
    • Restrictions
    • Examples:
      • and
      • json(b)
      • functions
  • Attributes
• Query Count /data/$model/$entity/query/count
• Common Mistakes

Returned values

The query will return "data" as well as meta-data:

{
    "meta": {
        "total": "11",      # (estimated) total number of entries in the table the user may select
        "filtered": "11",   # (estimated) number of rows in the current query
        "page": 1,          # selected page
        "page_size": "10",  # page size
        "selected": 10,     # rows on the page
        "huge_limit": "10"  # if total is greater than this value,
                            # filtered will might be an estimate and search,
                            # join and aggregation are restricted
        "conds": {          # search conditions used
            "me.name": "bla"
        },
        "attrs": { }        # search attributes used

    }
}

Estimated count values

For the total, if is $entity->{isHuge} in the universe is greater than $apiSettings->{isHugeLimit}, an estimated size will be returned.

Should isHuge be less than that, a count with a limit of huge_limit is done. Should total=huge_limit the table has at least this many entries, maybe more.

The filtered value is accurate if total < huge_limit. Otherwise, it will be counted with a limit of huge_limit. Therefore, if filtered=huge_limit, the query has at least this many result entries, maybe more.

page=1, selected=0 will means that the chosen filter does not return any rows.

When the api is in debug mode, 'generated_query' information isadded to the json reply. Please use them for debugging queries only.

"meta": {
    "generated_query": [
        "(SELECT me.modifying_action, me.modifying_client, me.modification_time, me.instance_entity, me.id, me.successor, me.context, me.key, me.value FROM ui.label() me LIMIT ?)",
        [
            {
                "sqlt_datatype": "integer"
            },
            "10"
        ]
     ]
}

Query-Context

General Query /data/$model/$entity/query

This is an interface for a DBIx:Class search on a table

https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/ResultSet.pm#search

It supports joining, sorting, filtering and returns a nested structure as a result.

Conds

The conds parameter defines the search conditions, which would generally be the "where" in an sql statement. The main logic is that the search parameters are presented in a data structure and things in arrays are OR'ed, and things in hashes are AND'ed.

https://metacpan.org/pod/release/ILMARI/SQL-Abstract-1.84/lib/SQL/Abstract.pm#WHERE-CLAUSES

Restrictions

Keys are restricted, as they are not bound, as values are.

They must either be a reference to a column or a call to one of the permitted functions.

Examples:

and

{
    "conds" : {
       "me.id" : 1,
       "me.name" : "Heidi"
    }
}

WHERE ( ( me.id = ? AND me.name = ? ) )

{
    "conds" : {
       "me.id" : {"<" : 5},
       "me.name" : "Heidi"
    }
}

WHERE ( ( me.id < ? AND me.name = ? ) )

#### or

{
    "conds" : {
        "-or" : [
            {"me.id" : {"<" : 5 }},
            {"me.name" : "Heidi"}
        ]
    }
}

WHERE ( me.id < ? OR me.name = ? ) )

json(b)

functions

POST /data/system/entity/query

{
  "conds": {
    "coalesce(me.id,1)": 1
  }
}

For more examples of search options see Query-Search-Options and Query-Examples.

Attributes

Query Count /data/$model/$entity/query/count

This endpoint takes the same parameter as a query but will return only the "meta" part of the query reply:

{
  "filtered": 3,
  "total": 4
  "attrs": {},
  "conds": { "is_active": true },
  "huge_limit": 1000000,
}

Common Mistakes

To query for a range, do not specify a hash key twice:

{"id" : {">":1}, "id" : {"<": 20}}                    # WRONG!
{"-and"   : [{"id": {">" : 1}}, {"id" : {"<" : 20}}]} # correct
{"id" : {"-between" : [1, 20]}}                       # correct and easy to read