CODEWITHSUNDEEP
sqlpostgresqlsupabasesupabase-database
Kishan Sripada
Kishan Sripada

Reputation: 87

Supabase: Query all rows in which either of two child relations contains property

Let's say I own a bunch of malls that have stores and restaurants. I want to query for all malls in which at least 1 store OR at least 1 restaurants is open

There are three tables, malls, stores and restaurants.

malls

id name
mall 1 Josh's Mall
mall 2 Sarah's Mall

stores

id name isOpen mallId (foreign key)
1 Express false mall 1
2 Banana Republic false mall 2

restaurants

id name isOpen mallId (foreign key)
1 PF Changs true mall 1
2 Panda Express false mall 2

Each individual store and restaurant is its own row within its respective table. Every store and restaurant contains a foreign key relation to the malls table that says that it belongs to that mall.

I want to query for all malls in which at least 1 store OR at least 1 restaurant is in stock. Since malls only have stores and restaurants, I'm basically querying malls that have something open

in this case the expected result would be Josh's mall since PF Changs is open

I've been working with this but it seems that you need to specify the foreign table for a .or() request.

      .from("malls")
      .select("name, store!inner( isOpen ), restaurants!inner( isOpen )")
      .or("store.isOpen.eq.true, restaurants.isOpen.eq.true")

When I want to specify the foreign table separately, how do I include both?

      .from("malls")
      .select("name, stores!inner( isOpen ), restaurants!inner( isOpen )")
      .or('isOpen.eq.true', { foreignTable: "stores" }) //but then where would i specify the restaurants table?

In psudo code, i want to do this

      .from("malls")
      .select("name, stores!inner( isOpen ), restaurants!inner( isOpen )")
     .or(
          .filter("stores.isOpen", "eq", true)
          .filter("restaurants.isOpen", "eq", true)
        )

Any help would be greatly appreciated!

Upvotes: 0

Views: 1518

Answers (1)

dshukertjr
dshukertjr

Reputation: 18640

It sounds like stores and restaurants are really the same thing with maybe slightly different attributes. Would it make sense to put them inside a same table like this?

venues

id name isOpen mallId (foreign key) type
1 Express false mall 1 store
2 Banana Republic false mall 2 store
3 Panda express false mall 2 restaurant

For the type, you can use enums in Postgres.

create type venu_type as enum ('store', 'restaurant');

This way you can easily query malls that have at least 1 venue open.

If you absolutely must separate the store and restaurants in different tables, you can create a view that combines the two to and perform or on the view. You can create a view like this:

create or replace view venues
as
    select
       id
       name
       is_open
       mall_id
    from restaurants
    union all
    select
       id
       name
       is_open
       mall_id
    from stores;

Once a view is created, you can query against the view just like you would query against a table using Supabase.

Upvotes: 2

Related Questions