Supabase•4y ago•

3 replies

How do I filter a table with foreign key using postgrest?

TLDR: I am looking postgrest equivalent of

 SQL
SELECT * 
FROM "movie_database"
WHERE "movie_database"."mov_id" IN (
  SELECT "genre".mov_id
  FROM "genre"
  where "genre".genre_val=[some_value]
)

 SQL
SELECT * 
FROM "movie_database"
WHERE "movie_database"."mov_id" IN (
  SELECT "genre".mov_id
  FROM "genre"
  where "genre".genre_val=[some_value]
)

---

I have two tables -

movie_database

movie_database

table and

genre

genre

table.

movie_database

movie_database

table has

mov_id

mov_id

primary key.

genre

genre

table has

mov_id

mov_id

(foreign key referring to movie_database) and

genre_val

genre_val

.

Right now I am doing

xyz.supabase.co/rest/v1/genre?genre_val=eq.[some_value]&select=movie_database(*)

xyz.supabase.co/rest/v1/genre?genre_val=eq.[some_value]&select=movie_database(*)

It would seem okay but I feel like it is joining rather than just pure filtering. Because when we do just filter without foreign key, I get

[
    {movie_database item 1},
    {movie_database item 2}
]

[
    {movie_database item 1},
    {movie_database item 2}
]

But right now I am getting following structure.

[
    {
        "movie_database": {movie_database item 1},
    },
    {
        "movie_database": {movie_database item 1},
    },
]

[
    {
        "movie_database": {movie_database item 1},
    },
    {
        "movie_database": {movie_database item 1},
    },
]

How do I filter a table with foreign key using postgrest?

TLDR: I am looking postgrest equivalent of

 SQL
SELECT * 
FROM "movie_database"
WHERE "movie_database"."mov_id" IN (
  SELECT "genre".mov_id
  FROM "genre"
  where "genre".genre_val=[some_value]
)

 SQL
SELECT * 
FROM "movie_database"
WHERE "movie_database"."mov_id" IN (
  SELECT "genre".mov_id
  FROM "genre"
  where "genre".genre_val=[some_value]
)

---

I have two tables -

movie_database

movie_database

table and

genre

genre

table.

movie_database

movie_database

table has

mov_id

mov_id

primary key.

genre

genre

table has

mov_id

mov_id

(foreign key referring to movie_database) and

genre_val

genre_val

.

Right now I am doing

xyz.supabase.co/rest/v1/genre?genre_val=eq.[some_value]&select=movie_database(*)

xyz.supabase.co/rest/v1/genre?genre_val=eq.[some_value]&select=movie_database(*)

It would seem okay but I feel like it is joining rather than just pure filtering. Because when we do just filter without foreign key, I get

[
    {movie_database item 1},
    {movie_database item 2}
]

[
    {movie_database item 1},
    {movie_database item 2}
]

But right now I am getting following structure.

[
    {
        "movie_database": {movie_database item 1},
    },
    {
        "movie_database": {movie_database item 1},
    },
]

[
    {
        "movie_database": {movie_database item 1},
    },
    {
        "movie_database": {movie_database item 1},
    },
]

How do I filter a table with foreign key using postgrest?

Similar Threads

How do I filter a table with foreign key using postgrest?

Similar Threads

Similar Threads

Similar Threads