Joining views together

[NeerajJai89]

Let's say there are two views: employee_info, employee_time_off. Is there a way to join these two views using PostgREST using something like ?
GET /view1?select=view1.*,view2.* &join=view2:id.eq.view1:id

[steve-chavez]

See https://postgrest.org/en/stable/references/api/resource_embedding.html#embedding-views

[NeerajJai89]

@steve-chavez If I understand this would return a nested response which would look something like this:
/employee?_embed=time_off(employee_id)
{
"employee_id": 1,
"name": "John Doe",
"email": "[email protected]",
...
"time_off": [
{
"time_off_id": 1,
"start_date": "2023-05-01",
"end_date": "2023-05-05",
...
},
{
"time_off_id": 2,
"start_date": "2023-06-01",
"end_date": "2023-06-05",
...
},
...
]
}

Would there be a way to get a flattened result

{
"employee_id": 1,
"name": "John Doe",
"email": "[email protected]",
"time_off_id": 1,
"start_date": "2023-05-01",
"end_date": "2023-05-05",
},{
"employee_id": 1,
"name": "John Doe",
"email": "[email protected]",
"time_off_id": 2,
"start_date": "2023-06-01",
"end_date": "2023-06-05"
}

[laurenceisla]

You can flatten the result with the spreading functionality in version 11.0.1 . See https://postgrest.org/en/stable/references/api/resource_embedding.html#spread-embedded-resource

Though, this only works for Many-To-One and One-To-One relationships, so you need to change the order of the embedding, which shouldn't be a problem since it would give you the same result that you mentioned in your example: /time_off?select=*,...employee(*)

[NeerajJai89]

Thanks for the quick response. I'm guessing for Many-Many (and other cases) relationships the only way would be creating a combined view of the underlying views ?

[laurenceisla]

Yes, that would be right.

[NeerajJai89]

Gotcha thanks