Many to Many Relationship Query (QUESTION - NOT A BUG)

[Manuelbaun]

Environment

• PostgreSQL version: PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
• PostgREST version: 8.0.0 with Supabase
• Operating system: Ubuntu 20.10 LXC Container

Description of issue

I did search beforehand and could not really find the answer I was looking for. Below is an example of an many to many relation between a song and its author over an song_author_relations table.

I want to query all songs of an author, where the author id is x.

How can I construct the query with postgrest from the table song? At the moment, I query the songs of an author over the author table like this:

/rest/v1/author?select=song(*)&id=eq.authorid1

I tried to get the songs with this query, but where I select all songs, and only the author data, where the author id equals to given value.

/rest/v1/song?select=*,author(id)&author.id=eq.authorid1

I don't care about the author, since I already have it, I just want to get the list of songs. Is that somehow possible? I feels like it should.

The minimum sql can be found below

create table song(
    id    text not null constraint song_pkey primary key,
    title text not null);

create table author(
    id  text not null constraint author_pkey primary key,
    first_name text  not null,
    last_name  text  not null);

create table song_author_relations (
    song_id text not null constraint song_author_relations_song_id_fkey references song on update cascade on delete restrict,
    author_id text not null constraint song_author_relations_author_id_fkey references author on update cascade on delete restrict,
    constraint song_author_relations_pkey primary key (author_id, song_id));


insert into song(id, title) VALUES ('songid1', 'The morning'),('songid2', 'The night');
insert into author(id, first_name, last_name) VALUES  ('authorid1', 'Max', 'Muster'), ('authorid1', 'Peter', 'Hase');
insert into song_author_relations(song_id, author_id) VALUES ('songid1', 'authorid1'),('songid2', 'authorid2');

[steve-chavez]

/rest/v1/song?select=*,author(id)&author.id=eq.authorid1

You want to filter the top level resource(songs) based on the embedded resource(author) right? I think you might be in need of this new feature: https://postgrest--442.org.readthedocs.build/en/442/api.html#top-level-filtering

This feature is not yet released, check this comment #1075 (comment).

[Manuelbaun]

thanks, then I have to use my workaround for now