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)	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
• Saved-Queries /data/$model/$entity/query?id=$id
• General Query /data/$model/$entity/query?conds={}&attrs={}
  • Conds
  • Attrs
    • Join and Prefetch
    • Ordering
    • Naming Conventions
    • Columns
    • Select and As
    • Collapse
• Common Mistakes

Returned values

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

{
    "meta": {
        "total": "11",      # (estimated) total size of table
        "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

Saved-Queries /data/$model/$entity/query?id=$id

General Query /data/$model/$entity/query?conds={}&attrs={}

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.

RESTRICTIONS: keys may not contain spaces.

Examples:

{
    "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 = ? ) )

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

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

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

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

Attrs

The attrs parameter is for attributes like sorting, joining and column definitions.

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

This document will cover some of the options with specific examples.

Join and Prefetch

RESTRICTIONS: keys and values may not contain spaces.

If a table column references another table column, use the column name to join it:

#/data/sytem/entity/query

{
    "attrs" : {"prefetch":"module"}
}

If a table is being references by another table, use $model__$entity__$attribute to join it:

(This will get all transitions which have the instance as pre_state)

#/data/system/state/query

{
    "attrs": {"prefetch" : "system__transition__pre_state"}
}

Combined with query conditions:

(This will get all transitions which have the instance as pre_state and whose post_state is 2)

#/data/system/state/query

{
    "conds" : {"system__transition__pre_state.post_state" : 2 },
    "attrs": {"prefetch" : "system__transition__pre_state"}
}

In contrast, a "join" instead of the "prefetch" will only return the data from the base table. The joined table(s) can be used in the search condition and only specific columns may be returned.

(select all columns from system.state and return only system.transition.name using system_transition_pre_state.post_state as search condition)

#/data/system/state/query

{
    "conds" : {"system__transition__pre_state.post_state" : 2 },
    "attrs": {
                "join"     : "system__transition__pre_state",
                "+columns" : ["system__transition__pre_state.entity"]
             }
}

Joins and prefetches can be nested as deeply as needed:

#/data/system/state/query

{
    "attrs": { "prefetch" : [  "modifying_client",
                             { "system__transition__pre_state" : "entity" }
                            ]
              }
}

More documentation on joining can be found here:

https://metacpan.org/pod/release/RIBASUSHI/DBIx-Class-0.082840/lib/DBIx/Class/Manual/Joining.pod

Ordering

RESTRICTIONS: keys and values may not contain spaces.

Ordering can be defined by the "order_by" attribute:

{attrs : { "order_by" : "date_of_birth" }}

{attrs : { "order_by" : { "-asc" : "col1" }}

{attrs : { "order_by": ["colA", {"-asc" : "colB"}]}

The API will also support similarity:

{attrs : { {"order_by" : {"-similarity" : ["me.name","abc"]}}}

will be translated to DBIx::Class notation \[ 'me.name <-> ?', "abc" ]

which in turn will be translated to sql ORDER BY me.name <-> ? : "abc"

Any column from a joined/prefetched table or a column selected with "select" can be used.

More details can be found here:

https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/ResultSet.pm#order_by and https://metacpan.org/pod/release/ILMARI/SQL-Abstract-1.84/lib/SQL/Abstract.pm#ORDER_BY_CLAUSES

Naming Conventions

When referencing to a column, the name without the table prefix can be used as long as it is unique within the query. Otherwise, the table name (as used for joining the table) needs to be prefixed: system__transition__pre_state.id.

The table the search is based on is always referenced as "me", so to dismbiguate use the "me" prefix, for joined/prefetched tables use their name as a prefix:

#/data/system/state/query

{
    "conds" : { "system__transition__pre_state.id" : { "-between" : [1,100] } } ,
    "attrs" : {"prefetch" : ["modifying_client", { "system__transition__pre_state" : "entity" }]}
}

Columns

RESTRICTIONS: keys and values may not contain spaces.

To get only the columns from a joined table:

"attrs" : {
    "join"    :"contracts__working_contract",
    "columns" : ["contracts__working_contract.working_hours_per_week","contracts__working_contract.salary"]
}

Use "+columns" to get all columns of the base table plus certain ones from joined tables:

    "join"     : "contracts__working_contract",
    "+columns" : ["contracts__working_contract.working_hours_per_week"]}

Select and As

RESTRICTIONS: keys and values may not contain spaces.

The "select" and "as" attribues allow even more flexibility to define exactly what should be returned than "columns" does.

"attrs" : {
    "join":   [ "contracts__working_time_constraint" ],
    "select": [ {"min" : "max_hours_per_day"} ],
    "as":     [ "min_max" ]
}

Generates:

SELECT MIN( max_hours_per_day )
FROM contracts.collective_agreement() me
LEFT JOIN contracts.working_time_constraint() contracts_working_time_constraint ON contracts_working_time_constraint.collective_agreement = me.id
LIMIT 10

Returns one row:

{
    "min_max": "9"
}

"+select" and "+as" are also available.

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

Collapse

When "collape" is set to a true value, indicates that any rows fetched from joined has_many relationships are to be aggregated into the corresponding "parent" object:

Parameters:

{
  "attrs" :
  {
    "collapse": "0",
    "join"    : ["contracts__working_time_constraint"],
    "+columns": ["contracts__working_time_constraint.id"]
  }
}

Reply:

[
    {
        "modification_time": "2017-09-20 08:38:17.652733",
        "name": "Kollektivvertrag Arbeiterinnen und Arbeiter im Hotel- und Gastgewerbe",
        "url": "https://www.wko.at/branchen/tourismus-freizeitwirtschaft/gastronomie/KV-Rahmen_Mai-2017.pdf",
        "instance_entity": "91",
        "contracts__working_time_constraint": {
            "id": "2"
        },
        "modifying_action": "325",
        "valid_from": "2017-01-05",
        "region": "1",
        "description": "Kollektivvertrag für Arbeiter im Hotel- und Gastgewerbe abgeschlossen zwischen dem Fachverband Gastronomie und dem Fachverband Hotellerie, beide 1045 Wien, Wiedner Hauptstraße 63, einerseits und der Gewerkschaft vida, 1020 Wien, Johann Böhm-Platz 1, andererseits.",
        "id": "1",
        "modifying_client": "1",
        "successor": null
    },
    {
        "modification_time": "2017-09-20 08:38:17.652733",
        "name": "Kollektivvertrag Arbeiterinnen und Arbeiter im Hotel- und Gastgewerbe",
        "url": "https://www.wko.at/branchen/tourismus-freizeitwirtschaft/gastronomie/KV-Rahmen_Mai-2017.pdf",
        "instance_entity": "91",
        "contracts__working_time_constraint": {
            "id": "1"
        },
        "modifying_action": "325",
        "valid_from": "2017-01-05",
        "region": "1",
        "description": "Kollektivvertrag für Arbeiter im Hotel- und Gastgewerbe abgeschlossen zwischen dem Fachverband Gastronomie und dem Fachverband Hotellerie, beide 1045 Wien, Wiedner Hauptstraße 63, einerseits und der Gewerkschaft vida, 1020 Wien, Johann Böhm-Platz 1, andererseits.",
        "modifying_client": "1",
        "id": "1",
        "successor": null
    }
]

Parameters:

{
  "attrs" :
  {
    "collapse": "1",
    "join"    : ["contracts__working_time_constraint"],
    "+columns": ["contracts__working_time_constraint.id"]
  }
}

Reply:

{
    "description": "Kollektivvertrag für Arbeiter im Hotel- und Gastgewerbe abgeschlossen zwischen dem Fachverband Gastronomie und dem Fachverband Hotellerie, beide 1045 Wien, Wiedner Hauptstraße 63, einerseits und der Gewerkschaft vida, 1020 Wien, Johann Böhm-Platz 1, andererseits.",
    "valid_from": "2017-01-05",
    "region": "1",
    "successor": null,
    "id": "1",
    "modifying_client": "1",
    "url": "https://www.wko.at/branchen/tourismus-freizeitwirtschaft/gastronomie/KV-Rahmen_Mai-2017.pdf",
    "modification_time": "2017-09-20 08:38:17.652733",
    "name": "Kollektivvertrag Arbeiterinnen und Arbeiter im Hotel- und Gastgewerbe",
    "instance_entity": "91",
    "contracts__working_time_constraint": [
        {
            "id": "1"
        },
        {
            "id": "2"
        }
    ],
    "modifying_action": "325"
}

Note that "prefetch" is a shortcut for "join", adding all columns from the joined related sources as "+columns" and setting "collapse" to a true value.

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

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