• Join and Prefetch
  • An attribute of an entity is a reference to an attribute of another entity
  • An attribute of an entity is being references by another attribute of a different entity
  • Restrictions
  • Examples
• Columns
  • Restrictions
  • Examples
    • columns
    • +columns
• Select and As
  • Restrictions
  • Examples
    • json
• Ordering
  • Restrictions
  • Examples
    • Function call
• Group By
  • Restrictions
  • Examples
• Collapse
  • Restrictions
    • Examples
• Having
  • Restrictions
  • Examples
• Distinct
  • Restrictions
  • Examples
• Permitted Column Names
• Permitted functions
• Naming Conventions

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

The IronAPI supports these options:

• join and prefetch
• (+)columns
• (+)select
• (+)as
• order_by
• group_by
• collapse
• having

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

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

'join' will join another entity to the base resultset without fetching any of it's attributes. Prefetch will join and fetch all the attributes of the other entity.

Only references may be joined/prefetched. There are two ways to do this, depending on the direction of the reference:

An attribute of an entity is a reference to an attribute of another entity

Use the attribute name to join or prefetch it - system.entity.module is a reference to system.module.id:

POST /data/sytem/entity/query

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

This fetches all the details of the linked module entry to each entity entry.

An attribute of an entity is being references by another attribute of a different entity

Use $model__$entity__$attribute to join it - system.module.id is being referenced by system.entity.module:

POST /data/system/module/query

{
  "attrs" : {"prefetch":"system__entity__module"}
}

This fetches all entity entries for a module and lists them under the module.

Restrictions

Keys and values may not contain spaces and must be found in the universe of the user.

Examples

Combined with query conditions:

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

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

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

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

Columns

columns defines which attributes should be fetched.

Restrictions

'attributes' may be a scalar or a list of scalars referencing one of the fetched attributes. E.g. "name", "me.name", ["me.name","module.id"]. It may also be a selector for a json(b) attribute: me.data->>'ident'.

Examples

columns

Get only two columns of the base entity:

POST /data/system/entity/query

{
  "attrs": {
    "columns": [
      "id",
      "name"
    ]
  }
}

To get only one column from a joined entity:

POST /data/system/entity/query

{
  "attrs": {
    "join": "module",
    "columns": [
      "module.name"
    ]
  }
}

+columns

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

{
  "attrs": {
    "join": "module",
    "+columns": [
      "module.name"
    ]
  }
}

Select and As

The "select" and "as" attribues allow even more flexibility to define exactly what should be returned than "columns" does. Other than renaming a column, permitted functions may be used to select calculated values.

For each entry in "select" a corresponding entry in "as" must be present to name the result.

Restrictions

Only permitted functions may be called. The value of the call might be itself a function call, like {"max" : "coalesce(me.id,1)"}. This function must again be in the list of permitted functions and it's parameters can only be column identifiers or values which match the regular expression used to check column names.

The value for "as" may be a path like sensor.sensor_type.name.

Examples

Only select one column and name it "me_id" from system.entity:

POST /data/system/entity/query

{
  "attrs": {
    "select": "me.id",
    "as": "me_id"
  }
}

Result:

{
  "data": [
    {
      "me_id": 1
    },
    {
      "me_id": 2
    }
  ]
}

Select the maximum id from system.entity:

POST /data/system/entity/query

{
  "attrs": {
    "select": [
      {
        "max": "me.id"
      }
    ],
    "as": [
      "max_id"
    ]
  }
}
}

Generates:

SELECT MAX( me.id ) FROM system.entity()

Returns one row:

{
  "data": [
    {
      "max_id": 99
    }
  ]
}

Use another function:

{
  "attrs": {
    "select": [
      {
        "max": "coalesce(me.id,1)"
       }
    ],
    "as": ["bla"]
  }
}

json

POST /data/extensions/client_meta_data/query

{
  "attrs" : {
    "select" : "settings->>'login_name'",
    "as" : "login_name"
  }
}

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

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

Ordering

Ordering can be defined by the "order_by" attribute.

Given	Will Generate
"colA"	ORDER BY colA
["colA", "colB"]	ORDER BY colA, colB
{"-asc" : "colA"}	ORDER BY colA ASC
{"-desc" => "colB"}	ORDER BY colB DESC
["colA", {-asc => "colB"}]	ORDER BY colA, colB ASC
{ "-asc" => ["colA","colB"] }	ORDER BY colA ASC, colB ASC
[ "FUNC(colA,?)", "bla" ]	ORDER BY FUNC(colA, ?) - with "bla bound to ?
[ { "-asc" => "colA" }, { "-desc" => ["colB"] }, { "-asc" => ["colC","colD"] }, [ "FUNC(colF, ?)", "bla" ], ]	ORDER BY, colA ASC, colB DESC, colC ASC, colD ASC, FUNC(colF, ?) - with "bla" bound to ?

The API also supports similarity for sorting.

Any column from a joined/prefetched table or a column selected with "select" can be used. A json selector on a column may also be used (e.g. me.data->>'ident').

More details can be found here: https://metacpan.org/pod/SQL::Abstract::Classic#ORDER-BY-CLAUSES

Restrictions

Keys may only be "-desc" or "-asc", values may either be a column reference or a permitted function call.

Examples

POST /data/system/entity/query

{
  "attrs": {
    "order_by": "name"
  }
}

To reverse the sort order from the default ascending to descending:

POST /data/system/entity/query

{
  "attrs": {
    "order_by": {"-desc" : "name"}
  }
}

Multiple sort parameters may be listed in the order they should be applied with:

POST /data/system/entity/query

{
  "attrs": {
    "order_by": ["name", {"-desc" : "modification_time"}]
  }
}

Function call

POST /data/system/entity/query

{
  "attrs": {
    "order_by": ["coalesce(me.id,?)",1]
  }
}

Similarity;

POST /data/system/entity/query

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

A column defined by select and as:

POST /data/system/entity/query

{
  "attrs": {
    "join": "module",
    "columns" : "me.id",
    "+select" : [{"count" : "module.id", "-as" : "module_count"}],
    "order_by": "module_count",
    "group_by" : "me.id"
  }
}

Group By

To group a result by one or more columns, group_by may be used.

Restrictions

The value may be either one string or an array of strings of permitted column names.

Examples

This will return a list of modules names and how many entites each modules has:

POST /data/system/module/query

{
  "attrs": {
    "join": "system__entity__module",
    "columns": "me.name",
    "+select": [
      {
        "count": "system__entity__module.id"
      }
    ],
    "+as": [
      "entity_count"
    ],
    "group_by": "me.name"
  }
}

A list of how many users have which value in the json attribute settings->>'enabled'.

POST /data/extensions/client_meta_data/query

{
  "attrs": {
    "select": [
      {
        "count": "me.owning_client"
      },
      "settings->>'enabled'"
    ],
    "group_by": "settings->>'enabled'"
  }
}

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:

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

Restrictions

The only valid values are 0 and 1.

Examples

This fetches all entities and lists them as an array for each module

POST /data/system/module/query

{
  "attrs": {
    "join": "system__entity__module",
    "columns": [
      "me.id",
      "me.name",
      "system__entity__module.id",
      "system__entity__module.name"
    ],
    "collapse": 1,
    "order_by" : ["me.id","system__entity__module.id"]
  }
}

{
  "data": [
    {
      "name": "system",
      "id": 1,
      "system__entity__module": [
        {
          "name": "module",
          "id": 2
        },
        {
          "name": "entity",
          "id": 3
        },
        {
          "id": 4,
          "name": "attribute"
        },
...

Without "collapse", there would be an entry for each module+entity combination:

{
  "data": [
    {
      "id": 1,
      "name": "system",
      "system__entity__module": {
        "id": 2,
        "name": "module"
      }
    },
    {
      "id": 1,
      "name": "system",
      "system__entity__module": {
        "id": 3,
        "name": "entity"
      }
    },
    {
      "id": 1,
      "name": "system",
      "system__entity__module": {
        "id": 4,
        "name": "attribute"
      }
    },
...

Having

having specifies a search condition for a group or an aggregate.

For filtering with a function, this format is supported: {'count(id)' => {"!=", 1}}. It will be translated to SQL \[ 'count(id) != ?', 100 ]. Only (permitted functions)[#permitted-functions] may be called.

https://metacpan.org/pod/DBIx::Class::ResultSet#having

Restrictions

Only (permitted functions)[#permitted-functions] may be called. As operators, !=. =, < , <=, > and >= are currently supported. The value for the comparison must be integer.

Examples

List only those modules which have entites attached:

POST /data/system/module/query

{
  "attrs": {
    "join": "system__entity__module",
    "columns": "me.name",
    "+select": [
      {
        "count": "system__entity__module.id",
        "-as" : "entity_count"
      }
    ],
    "+as": [
      "entity_count"
    ],
    "group_by": "me.name",
    "having" : {"count(system__entity__module.id)" : {">" : 0}}
  }
}

Distinct

Set to 1 to automatically generate a "group_by" clause based on the selection.

https://metacpan.org/pod/DBIx::Class::ResultSet#having

Restrictions

The only values allowed are 0 or 1.

Examples

POST /data/system/module/query

{
  "attrs": {
    "join": "system__entity__module",
    "+select": [
      {
        "count": "system__entity__module.id",
        "-as" : "entity_count"
      }
    ],
    "+as": [
      "entity_count"
    ],
    "distinct" : 1
  }
}

This will add a group by all column names of system.module to the query:

SELECT
 me.id, me.name, me.label, me.description, me.modifying_action, me.modifying_client, me.modification_time, me.instance_entity, me.successor,
 COUNT( system__entity__module.id ) AS entity_count
FROM system.module() me
LEFT JOIN system.entity system__entity__module ON system__entity__module.module = me.id
GROUP BY me.id, me.name, me.label, me.description, me.modifying_action, me.modifying_client, me.modification_time, me.instance_entity, me.successor

Permitted Column Names

Wherever a column is defined, it may be a simple name if it's unique e.g. id, or with the entity prefix me.id. For json(b) columns, a selector may be defined: data->>'ident' or me.data->>'ident'.

Permitted functions

For any attribute which allows to call a function, this function must be permitted. Apart from

• coalesce
• count
• least
• min
• max

other permitted functions may be listed for a project. The list of function can be found in the unverse:

  "apiSettings": {
    "allowedQueryFunctions": "bla.blubb, coalesce, count, flubb, least, max, min",
    ...
  },

The function parameters may be anything conforming to the column names regex (so currently apart from column references integer and real numbers and 'null').

The whole function call may not contain spaces. E.g. coalesce(me.id,1) must not be written as coalesce(me.id, 1).

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" }]}
}