split queries for embedded resources when using alias

[hugomrdias]

Example :

https://pg-alpha.movves.io/user?select=name,type,spaces:space{*,levels:level{*,geometry::json},entrances:access{*,geometry::json},zones:zone{*,geometry::json},stores:zone{*,geometry::json}}
&id=eq.687
&space.level.disabled_at=is.null
&space.level.deleted_at=is.null
&space.level.active_for_metrics=is.true
&space.access.disabled_at=is.null
&space.access.deleted_at=is.null
&space.access.active_for_metrics=is.true
&space.zones.zone_type_id=eq.2
&space.zones.disabled_at=is.null
&space.zones.deleted_at=is.null
&space.zones.active_for_metrics=is.true
&space.stores.zone_type_id=eq.3
&space.stores.disabled_at=is.null
&space.stores.deleted_at=is.null
&space.stores.active_for_metrics=is.true
&space.disabled_at=is.null
&space.deleted_at=is.null

if you select with alias (zones:zone and stores:zone) and filter without (space.zone.zone_type_id=eq.2) only one of the selects gets the result of the query ( space.zones will be empty and space.stores will have the result).

if you select and filter with alias (as in the url above) space.zone and space.stores will both have the same result instead of space.zones having only space.zone.zone_type_id=eq.2 and space.stores space.zone.zone_type_id=eq.3.

[ruslantalpa]

seems like you have two fk columns pointing to the same table.
try specifying the name of the column instead of the table.
also check if this recent feature helps #918

[steve-chavez]

Not related to #918. The issue here is that aliased embeds cannot be used aliased in filters.
I've come up with a reduced test case:

create table test.space(
  id integer primary key,
  name text
);

create table test.zone(
  id integer primary key,
  name text,
  zone_type_id integer,
  space_id integer references test.space(id)
);

insert into test.space values (1, 'space 1');
insert into test.zone values (1, 'zone 1', 2, 1), (2, 'zone 2', 2, 1), (3, 'store 3', 3, 1), (4, 'store 4', 3, 1);

The wanted response is:

   {
      "id":1,
      "name":"space 1",
      "zones":[
         {
            "id":1,
            "name":"zone 1",
            "zone_type_id":2
         },
         {
            "id":2,
            "name":"zone 2",
            "zone_type_id":2
         }
      ],
      "stores":[
         {
            "id":3,
            "name":"store 3",
            "zone_type_id":3
         },
         {
            "id":4,
            "name":"store 4",
            "zone_type_id":3
         }
      ]
   }
]

Reproducing the errors:

if you select with alias (zones:zone and stores:zone) and filter without (space.zone.zone_type_id=eq.2) only one of the selects gets the result of the query ( space.zones will be empty and space.stores will have the result).

GET /space?select=id,name,zones:zone(id,name,zone_type_id),stores:zone(id,name,zone_type_id)
&zone.zone_type_id=eq.2
&zone.zone_type_id=eq.3

[
   {
      "id":1,
      "name":"space 1",
      "zones":[],
      "stores":[
         {
            "id":1,
            "name":"zone 1",
            "zone_type_id":2
         },
         {
            "id":2,
            "name":"zone 2",
            "zone_type_id":2
         },
         {
            "id":3,
            "name":"store 3",
            "zone_type_id":3
         },
         {
            "id":4,
            "name":"store 4",
            "zone_type_id":3
         }
      ]
   }
]

The problem here is that the filters are applied to only the first embed.

if you select and filter with alias (as in the url above) space.zone and space.stores will both have the same result instead of space.zones having only space.zone.zone_type_id=eq.2 and space.stores space.zone.zone_type_id=eq.3.

GET /space?select=id,name,zones:zone(id,name,zone_type_id),stores:zone(id,name,zone_type_id)
&zones.zone_type_id=eq.2
&stores.zone_type_id=eq.3

[
   {
      "id":1,
      "name":"space 1",
      "zones":[
         {
            "id":1,
            "name":"zone 1",
            "zone_type_id":2
         },
         {
            "id":2,
            "name":"zone 2",
            "zone_type_id":2
         },
         {
            "id":3,
            "name":"store 3",
            "zone_type_id":3
         },
         {
            "id":4,
            "name":"store 4",
            "zone_type_id":3
         }
      ],
      "stores":[
         {
            "id":1,
            "name":"zone 1",
            "zone_type_id":2
         },
         {
            "id":2,
            "name":"zone 2",
            "zone_type_id":2
         },
         {
            "id":3,
            "name":"store 3",
            "zone_type_id":3
         },
         {
            "id":4,
            "name":"store 4",
            "zone_type_id":3
         }
      ]
   }
]

The problem here is that the aliased filters(zones and stores) are ignored and not considered in the query since they are not associated with any embedded node, this is mentioned in https://github.com/begriffs/postgrest/blob/master/src/PostgREST/DbRequestBuilder.hs#L276.