Skip to content

GitLab

Attributes · Changes

Page history
Create API/Queries/Attributes authored by Andrea Pavlovic's avatar Andrea Pavlovic
Hide whitespace changes
Inline Side-by-side
API/Queries/Attributes.md 0 → 100644
View page @ b10d6a8b
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.
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:
```JSON
#/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)
```JSON
#/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)
```JSON
#/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)
```JSON
#/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:
```JSON
#/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](https://www.postgresql.org/docs/9.6/static/pgtrgm.html):
`{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"](#select-as) 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:
```JSON
#/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:
```JSON
"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:
```JSON
"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.
```JSON
"attrs" : {
"join": [ "contracts__working_time_constraint" ],
"select": [ {"min" : "max_hours_per_day"} ],
"as": [ "min_max" ]
}
```
Generates:
```SQL
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:
```JSON
{
"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:
```JSON
{
"attrs" :
{
"collapse": "0",
"join" : ["contracts__working_time_constraint"],
"+columns": ["contracts__working_time_constraint.id"]
}
}
```
Reply:
```JSON
[
{
"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:
```JSON
{
"attrs" :
{
"collapse": "1",
"join" : ["contracts__working_time_constraint"],
"+columns": ["contracts__working_time_constraint.id"]
}
}
```
Reply:
```JSON
{
"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