Resource Embedding - Enable filtering on a parent table of an embedded child tables properties

[a-mckinley]

This is a bit hard to explain in the title, hopefully the example below will make the issue clear.

postgres version: PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) (Docker)
postgrest version: postgrest/postgrest:v8.0.0 (Docker)

I am trying to maximise my usage of resource embedding and have run into a scenario that doesn't seem possible to achieve at the minute without a workaround.

I created the simple schema below to illustrate the issue.

-- the main table
create table public_api.parent (
    id bigint not null generated always as identity primary key,
    data text
);

-- the normalised config of each piece of child data
create table public_api.child_config (
    id bigint not null generated always as identity primary key,
    name text
);

-- data related to the parent objects
create table public_api.child (
    id bigint not null generated always as identity primary key,
    data text,
    child_config_id bigint not null,
    parent_id bigint not null,
    foreign key(parent_id) references public_api.parent(id),
    foreign key(child_config_id) references public_api.child_config(id)
);

insert into public_api.child_config(name) values ('property 1');
insert into public_api.child_config(name) values ('property 2');

insert into public_api.parent(data) values ('parent data');

insert into public_api.child(parent_id,child_config_id,data)
values (
    (select id from public_api.parent where data = 'parent data'),
    (select id from public_api.child_config where name = 'property 1'),
    'property 1 value'
);

insert into public_api.child(parent_id,child_config_id,data)
values (
    (select id from public_api.parent where data = 'parent data'),
    (select id from public_api.child_config where name = 'property 2'),
    'property 2 value'
);

What I would like to do is get parent with id=1 and the related child objects but only those with name = 'property 1' which is from the child_config table.

Here is the the REST request that I have tried to achieve this.

GET http://127.0.0.1:3001/parent
    ?select=*,child(data,child_config(name))
    &id=eq.1
    &child_config.name=eq.property 1
Content-Type: application/json
Accept: application/vnd.pgrst.object+json
Authorization: Bearer {{jwt}}

This generated a query as follows:

SELECT "public_api"."parent".*,
        COALESCE (
            (
                SELECT json_agg("parent".*)
                FROM (
                        SELECT "public_api"."child"."data",
                            row_to_json("child_child_config".*) AS "child_config"
                        FROM "public_api"."child"
                            LEFT JOIN LATERAL(
                                SELECT "public_api"."child_config"."name"
                                FROM "public_api"."child_config"
                                WHERE "public_api"."child"."child_config_id" = "public_api"."child_config"."id"
                            ) AS "child_child_config" ON TRUE
                        WHERE "public_api"."parent"."id" = "public_api"."child"."parent_id"
                    ) "parent"
            ),
            '[]'
        ) AS "child"
    FROM "public_api"."parent"
    WHERE "public_api"."parent"."id" = $1

The first thing that I noticed here is that the 2nd filter parameter on child_config is not set at all.

I think a query that would work here looks like this (with an inner lateral join and filter applied in that subquery)

SELECT "public_api"."parent".*,
        COALESCE (
            (
                SELECT json_agg("parent".*)
                FROM (
                        SELECT "public_api"."child"."data",
                            row_to_json("child_child_config".*) AS "child_config"
                        FROM "public_api"."child"
                            INNER JOIN LATERAL(
                                SELECT "public_api"."child_config"."name"
                                FROM "public_api"."child_config"
                                WHERE "public_api"."child"."child_config_id" = "public_api"."child_config"."id"
                                AND "public_api"."child_config"."name" = 'property 1'
                            ) AS "child_child_config" ON TRUE
                        WHERE "public_api"."parent"."id" = "public_api"."child"."parent_id"
                    ) "parent"
            ),
            '[]'
        ) AS "child"
    FROM "public_api"."parent"
    WHERE "public_api"."parent"."id" = 1

I am currently working around the issue using a view defined like this (incorporating the inner join)

-- adding this view enables filtering on the child_config name property
create view public_api.child_with_config_view as
select c.*,cc.name
from public_api.child c
inner join public_api.child_config cc on c.child_config_id = cc.id;

And subsequently making a request like this (which works well):

GET http://127.0.0.1:3001/parent
    ?select=*,child_with_config_view(data,name)
    &id=eq.1
    &child_with_config_view.name=eq.property 1
Content-Type: application/json
Accept: application/vnd.pgrst.object+json
Authorization: Bearer {{jwt}}

Is this a limitation of resource embedding at the minute, or I am missing something here that would enable this without the extra view?

[wolfgangwalther]

I only skimmed your post so far, but maybe you're just missing the correct filter here:

    &child_config.name=eq.property 1

Because you're using nested embedding, I think this should be:

    &child.child_config.name=eq.property 1

I'm not sure whether that's actually documented, yet, but there are test-cases that suggest this should work:

postgrest/test/Feature/QuerySpec.hs

Lines 237 to 240 in 243e4f0

	it "matches filtering nested items" $
	get "/clients?select=id,projects(id,tasks(id,name))&projects.tasks.name=like.Design*" `shouldRespondWith`
	[json|[{"id":1,"projects":[{"id":1,"tasks":[{"id":1,"name":"Design w7"}]},{"id":2,"tasks":[{"id":3,"name":"Design w10"}]}]},{"id":2,"projects":[{"id":3,"tasks":[{"id":5,"name":"Design IOS"}]},{"id":4,"tasks":[{"id":7,"name":"Design OSX"}]}]}]|]
	{ matchHeaders = [matchContentTypeJson] }

[a-mckinley]

Thanks @wolfgangwalther you were right about the nested embeddings.

After correcting the nested filter the core of the generated query looks like

SELECT "public_api"."parent".*,
        COALESCE (
            (
                SELECT json_agg("parent".*)
                FROM (
                        SELECT "public_api"."child"."data",
                            row_to_json("child_child_config".*) AS "child_config"
                        FROM "public_api"."child"
                            LEFT JOIN LATERAL(
                                SELECT "public_api"."child_config"."name"
                                FROM "public_api"."child_config"
                                WHERE "public_api"."child_config"."name" = 'property 1'
                                    AND "public_api"."child"."child_config_id" = "public_api"."child_config"."id"
                            ) AS "child_child_config" ON TRUE
                        WHERE "public_api"."parent"."id" = "public_api"."child"."parent_id"
                    ) "parent"
            ),
            '[]'
        ) AS "child"
    FROM "public_api"."parent"
    WHERE "public_api"."parent"."id" = '1'

However, this still retrieves all the child records independent of the child_config name. Unless there is another mechanism that I am missing, the solution for this case seems to be making the left join lateral optionally an inner join lateral.

This modified core query produces the output data I was hoping for.

SELECT "public_api"."parent".*,
        COALESCE (
            (
                SELECT json_agg("parent".*)
                FROM (
                        SELECT "public_api"."child"."data",
                            row_to_json("child_child_config".*) AS "child_config"
                        FROM "public_api"."child"
                            INNER JOIN LATERAL(
                                SELECT "public_api"."child_config"."name"
                                FROM "public_api"."child_config"
                                WHERE "public_api"."child_config"."name" = 'property 1'
                                    AND "public_api"."child"."child_config_id" = "public_api"."child_config"."id"
                            ) AS "child_child_config" ON TRUE
                        WHERE "public_api"."parent"."id" = "public_api"."child"."parent_id"
                    ) "parent"
            ),
            '[]'
        ) AS "child"
    FROM "public_api"."parent"
    WHERE "public_api"."parent"."id" = '1'

Maybe there is something like the foreign key hint syntax that could be used for this?

[wolfgangwalther]

However, this still retrieves all the child records independent of the child_config name. Unless there is another mechanism that I am missing, the solution for this case seems to be making the left join lateral optionally an inner join lateral.

This is not implemented, yet. As we're dealing with this in #1075 and #1949, I'll close this issue here (or rather make it a discussion for Q & A), as the core issue about nested filters seems to be solved.

Regarding documentation, we already have PostgREST/postgrest-docs#432 open, so that will be taken care of eventually, too.