Andrea PavlovicDBIX::Class Inbuilts
(not) NULL
To search for (not) NULL entries, use the null value in json:
POST /data/extensions/client_meta_data/query
{
"conds": {
"me.api_key": null
}
}{
"conds": {
"me.api_key": {
"-not": null
}
}
}IDENT - comparing two columns
This query will return all entries where name=label.
POST /data/system/entity/query
{
"conds": {
"me.name": {
"-ident": "me.label"
}
}
}WHERE ( me.name = me.label )http://search.cpan.org/~ilmari/SQL-Abstract-1.84/lib/SQL/Abstract.pm#-ident
LIKE and ILIKE
POST /data/system/entity/query
{ "conds" : {"me.name" : {"-like": "%a%"}} }
{ "conds" : {"me.name" : {"-not_like": "%a%"}} }
{ "conds" : {"me.name" : {"-ilike": "%a%"}} }
{ "conds" : {"me.name" : {"-not_ilike": "%a%"}} }WHERE ( me.name like ? )
WHERE ( me.name not like ? )Regex
The regex operators (~, !~, ~*, !~*) are supported:
POST /data/system/entity/query
{
"conds" : {"me.name" : {"~*": "module.name"}}
}WHERE ( me.name ~* ? )If a column on a big table is not indexed correctly, this search option will not be allowed.
(not) IN
Search for a list of values.
POST /data/system/entity/query
{ "conds" : {"me.name" : {"-in": [1,2]}} }
{ "conds" : {"me.name" : {"-not_in": [3,4]}} }IronAPI Extensions
-in_function
This is an extension for IronAPI.
Instead of defining an array of ids for the "in" statement, get an array of ids from a function. This will only work if the function used has explicitly been listed as permitted for this.
{
"conds": {
"me.id": {
"-in_function": "core.assignment_ids_for_unit_instance(12)"
}
}
} "me.id" IN (SELECT * FROM core.assignment_ids_for_unit_instance(12))-similarity
This is an extension for IronAPI.
This sorts the result by how "close" it is to the supplied text. See the Similarity Postgres Documentation.
POST /data/system/entity/query
{
"attrs": {
"order_by": {
"-similarity": [
"me.name",
"constant"
]
},
"columns": "me.name"
}
}This will be translated to sql ORDER BY me.name <-> ? : "constant".
If a column on a big table is not indexed correctly, this search option will not be allowed.
-tsquery
This is an extension for IronAPI.
It uses Postgres' fulltext search with tsvecor and tsquery.
To search for "data" within the names of entites, which we know are written in "english":
POST /data/system/entity/query
{
"conds" : {"-tsquery" : ["english","me.name","data"] }
}will be translated to
WHERE ( to_tsvector(?,me.name) @@ to_tsquery(?, ? ) ): 'english', 'english', 'data'If a column on a big table is not indexed correctly, this search option will not be allowed.
JSONB contains (@>)
This is an extension for IronAPI. Does the left JSONB value contain the right JSONB path/value entries at the top level?
POST /data/extensions/client_meta_data/query
{
"conds": {
"me.settings": {
"@>": {
"login_name": "test"
}
}
}
}JSONB Key exists (?)
This is an extension for IronAPI. Does the JSONB this at the top level?
{
"conds": {
"data" : {"?" : "temperature_person"}
}
}-subtree
This is an extension for IronAPI.
For entities which represent a tree structure ("isTree": true in the universe), a search for a subtree of an element possible. The following will return all entries of the current entity which are "under" id 1, i.e. have either "1" or any of it's (children's) children as their parent:
{
"conds": {"me.id": {"-subtree": [1] }
}The search may be limited to a certain depth, in this example it's "2", so only the main entry (with id "1") and it's direct children will be returned:
{
"conds": {"me.id": {"-subtree": [1, 2] }
}It can be combined with other search options, like this for example with "or":
{
"conds": [
{"me.id": {"-subtree": [1,2]}},
{"me.name" : {"-ilike" : "%a%"}}
]
}with "and":
{
"conds":
{"-and" : [
{"me.id": {"-subtree": [1,2]}},
{"me.name" : {"-ilike" : "%a%"}}
]
}
}-parents
This is an extension for IronAPI.
Similar to -subtree, this allows to select the parent entries of an instance with the last entry being the last parent the user has access to.
{
"me.id" : {"-parents" : 5}
}