0

I'm new to postgREST. I've set it up and it's working fine with my db. I'm going through the docs and I think I could use of Resource Embedding, but I can't figure out how to make it work in a nested way.

My schema has tables similar to the following:

create table ta (
    a_id integer primary key,
    a_desc varchar(50)
);

create table tb (
    b_id integer primary key,
    a_id integer not null,
    b_desc varchar(50),
    constraint tb_fk1 foreign key (a_id) references ta(a_id)
);

create table tc (
    c_id integer primary key,
    b_id integer not null,
    c_desc varchar(50),
    constraint tc_fk1 foreign key (b_id) references tb(b_id)
);

insert into ta values (1, 'a1');

insert into tb values (1, 1, 'b1');
insert into tb values (2, 1, 'b2');

insert into tc values (1, 1, 'c1');
insert into tc values (2, 1, 'c2');
insert into tc values (3, 2, 'c3');
insert into tc values (4, 2, 'c4');

Resource embedding works when I select ta an tb:

localhost:3000/ta?select=*,tb(*)

[
    {
        "a_id": 1,
        "a_desc": "a1",
        "tb": [
            {
                "b_id": 1,
                "a_id": 1,
                "b_desc": "b1"
            },
            {
                "b_id": 2,
                "a_id": 1,
                "b_desc": "b2"
            }
        ]
    }
]

It also works with tb and tc:

localhost:3000/tb?select=*,tc(*)
[
    {
        "b_id": 1,
        "a_id": 1,
        "b_desc": "b1",
        "tc": [
            {
                "c_id": 1,
                "b_id": 1,
                "c_desc": "c1"
            },
            {
                "c_id": 2,
                "b_id": 1,
                "c_desc": "c2"
            }
        ]
    },
    {
        "b_id": 2,
        "a_id": 1,
        "b_desc": "b2",
        "tc": [
            {
                "c_id": 3,
                "b_id": 2,
                "c_desc": "c3"
            },
            {
                "c_id": 4,
                "b_id": 2,
                "c_desc": "c4"
            }
        ]
    }
]

But I don't know how to make it work from ta through tc, kind of combining both queries.

Does anyone know how I could achieve this? Preferably using a query string, but working with views or stored procedures would be ok too.

Thanks in advance for any help on this.

PS: Using Potstgres 12 and postgREST 7

Awer Muller
  • 557
  • 7
  • 17
  • Have you tried `localhost:3000/ta?select=*,tb(*, tc(*))`? – Steve Chavez Aug 28 '20 at 16:13
  • @SteveChávez I just did and it worked exactly the way I wanted. Could you please point me to the docs where that sintax is explained? I just reread the docs for resource embedding and couldn't find it. Also if you make it an answer I'll accept it. Thank you very much for your help! – Awer Muller Aug 28 '20 at 16:27
  • Glad to help! Currently, the docs are missing a section about nested resource embedding: https://github.com/PostgREST/postgrest-docs/issues/275. We'll fix that on the upcoming release docs. – Steve Chavez Aug 28 '20 at 17:03

1 Answers1

2

For nested resource embedding, you can do:

GET localhost:3000/ta?select=*,tb(*,tc(*))
Steve Chavez
  • 931
  • 10
  • 13