S
Supabase•2y ago
hellflur

SQL to Supabase query

Apologies if this has already been answered. I could not work out how to do this. I'm trying to query a category by its slug and return the associated articles in the category. I have a join table categories_articles that contains an id (id of the category) and item (id of the article) The following SQL query works: 
SELECT a.*
FROM articles a
INNER JOIN categories_articles ca ON a.id = CAST(ca.item AS INTEGER)
INNER JOIN categories c ON ca.categories_id = c.id
WHERE c.slug = 'the-category-slug';
SELECT a.*
FROM articles a
INNER JOIN categories_articles ca ON a.id = CAST(ca.item AS INTEGER)
INNER JOIN categories c ON ca.categories_id = c.id
WHERE c.slug = 'the-category-slug';
Any tips for how to do this with the Supabase API?
4 Replies
garyaustin
garyaustin•2y ago
The API can automatically detect joins thru a join table as long as both fk's are part of the primary key. So .select('*,categories(*)') Is the basic join relationship. Then you can filter with something like .eq('categories.slug','the-slug')
hellflur
hellflurOP•2y ago
Thanks for the help but it didn't work, most likely due to the FKs not being part of the primary key. I'm using Directus as the visual layer to Supabase. So it seems the way Directus creates M2M and M2A relationships is not the way Supabase would like them to be set.
garyaustin
garyaustin•2y ago
It does not have a way to force the primary keys?
hellflur
hellflurOP•2y ago
Anyone else with the same question, I solved it with: 
const { data: category, error } = await client
.from('categories')
.select('*, categories_articles(articles(title, slug))')
.eq('slug', slug)
.single()
const { data: category, error } = await client
.from('categories')
.select('*, categories_articles(articles(title, slug))')
.eq('slug', slug)
.single()
Thanks for your help Gary

Did you find this page helpful?