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

Question

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!

Hello! I hope it's not too late, but I have a similar problem, did you manage to solve it? And how did you do it? — Izlia, Sep 29 '22 at 04:33

dshukertjr · Answer 1 · 2022-07-21T00:56:31.203

2

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.

edited Jul 21 '22 at 00:56

answered Jul 20 '22 at 06:25

dshukertjr

15,244
11
57
94

for organizational purposes, i'd prefer to keep them in separate tables. I'm not sure where to go from here. I was thinking to union open resurants and open stores in the sql query? what are your thoughts on that – Kishan Sripada Jul 20 '22 at 20:25
@KishanSripada If you really want to separate the tables, then you can create a view that combines the two. I will edit my answer right now to show you how you can do that. – dshukertjr Jul 21 '22 at 00:52
will the view update to reflect changing data in both tables? – Kishan Sripada Jul 21 '22 at 16:13
@KishanSripada Yup, the view will dynamically update in real time as either one of the table updates! – dshukertjr Jul 22 '22 at 04:45

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

1 Answers1