Andrea PavlovicThe 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"].
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
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 10Returns one row:
{
"min_max": "9"
}"+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 ? / [ | ORDER BY { "-asc" => "colA" }, | colA ASC, { "-desc" => ["colB"] }, | colB DESC, { "-asc" => ["colC","colD"] },| colC ASC, colD ASC, [ "FUNC(colF, ?)", "bla" ], | 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.
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
Restrictions
Keys and values may not contain spaces.
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"}]
}
}Similarity;
POST /data/system/entity/query
{
"attrs": {
"order_by": {
"-similarity": [
"me.name",
"constant"
]
},
"columns": "me.name"
}
}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
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" }]}
}