I have two tables and i want to join them and show the table 2 data in the collection list of table 1, now iam using 2 collections table 2 is nested in table 1 which is causing the weweb to run slow, so kindly can someone help me out in making the following query runnable in weweb environment
SELECT a.id, a.show_id, a.episode_name, a.description FROM “APPLE_EPISODES” a
INNER JOIN “APPLE_SHOWS” b on a.show_id = b.id
WHERE published_date > CURRENT_DATE - interval ‘1 month’
AND (episode_name like ‘%python%’ or description like ‘%python%’)
Are you using the SQL plugin or is this something you’re trying to achieve in Supabase?
It’s flagged supabase so I think it’s supabase indeed.
What you want to do is go to Advanced in your Configuration of your Collection and do a query based on these docs:
https://postgrest.org/en/stable/references/api/resource_embedding.html
If you give me more info about your schema (I can’t see in which table published_date is), I could give you a more precise info.
Hi, your query seems to be doable with the UI, whats the issue ?
The advanced mode allow you to write yourself the select part, instead of using our selector, if you need to embed things. But your query doesn’t contain any embed, you’re selecting only table A fields, so you don’t need the advanced mode.
I don’t really understand your INNER JOIN, as you’re not selecting any data or filtering from the second table. You can simply add a condition to check if show_id exist instead, right ?
Can’t do inner joins with UI, or is it possible now?
I think published date, current date might be from the joined table?
Oh duh, I missed that! 
So yeah, @mujtaba_khan, I think you could use the Advanced mode of your Supabase collection in WeWeb to get the published date from another table.
Then you could use the filters on the collection to add your WHERE conditions:
This is the approach. Unless the data you want to filter by is from the foreign table. But yeah, let us know how it goes! 
1 Like