"Inner Join" search query with sorting

PANTARIS Platform Questions
1

Is it possible to create a search query that is analogous to the following SQL query:

select vehicles.*, project.name from vehicles inner join projects on project.id = vehicles.project_id order by project.name

1 Like
2

Yes, you can include relations (docs). For your example: https://api.calponia.com/v2/vehicles/search?include=project. You will find included in the response, which contains the related projects.

It’s also possible to filter based on relations:

curl --location --request POST 'https://api.calponia.com/v2/vehicles/search?include=project' \
--header 'Authorization: Token xxx' \
--header 'Content-Type: application/json' \
--data-raw '{
    "inlineData": true,
    "query": {
        "name": {"_eq": "test"},
        "_rel": {
            "project": {
                "name": { "_eq": "test"}
            }
        }
    },
    "limit": 5,
    "offset": 0
}'

Keep in mind, that this can result in an amount of data. So make sure to define the query as close as possible and set limit+offset to matching size.

Searches can also be sorted → docs

1 Like
3

Thanks for the answer. The “rel” syntax isn’t contained in the docs though (unless I’m blind what is entirely possible :wink:), perhaps somebody should put it in there.

The sort examples in the docs only refer to direct attributes of the searched object. What about sorting by included attributes? In your above example, is it possible to sort by project name and how would the syntax look in that case?

4

hm, maybe not offically supported. Or undocumented.

I think sorting is only for the ressource itself.

5

Kannte ich auch nicht. Interessant :slight_smile: @thomas.schuerle my tests indicated yesterday that sorting the resource itself also sorted the included relation. Maybe just coincidentally.

6

Ok, thanks.

7

Just tried the “rel” syntax: it must be “_rel”, not “rel”.

2 Likes