Supabase: Filter data with join and logical 'OR'

Question

I'm using Supabase as a database and trying to implement a full-text search.

My example setup is quite simple, I have two tables:

items

+----+-----------+-----------------+
| id | name      | manufacturer_id |
+----+-----------+-----------------+
| 1  | Notebook  | 1               |
+----+-----------+-----------------+
| 2  | Mouse     | 2               |
+----+-----------+-----------------+

manufacturers

+----+-----------+
| id | name      |
+----+-----------+
| 1  | Apple     |
+----+-----------+
| 2  | Microsoft |
+----+-----------+

My goal: search for item names or manufacturer names and always receive the respective items. Searching for 'Apple' would return all items whose manufacturers name contain this phrase. Searching for 'Notebook' would return all items with this name.

(I simplified this example by using exact matches because this is not the problem I'm stuck with).

My current approach is the following:

let keyword = 'Apple';

supabase
  .from('items')
  .select('*, manufacturers!inner(*)')
  .or(`name.eq.${term}`)
  .or(`name.eq.${term}`, { foreignTable: 'manufacturers' })

Although this query does not return anything.

If I remove .or('name.eq.${term}') it return the correct item. Same if I remove the second .or() and use an item name as my keyword.

I simply can't find a way to combine two OR operators. I also tried multiple filter() queries but was not successfully.

EDIT: The (working) SQL syntax would be the following:

SELECT * FROM items 
   JOIN manufacturers ON items.manufacturer_id = manufacturers.id
   WHERE items.name = 'Apple' OR manufacturers.name = 'Apple'

Does anyone have an approach on how to do that with the Supabase JavaScript SDK? Thank you in advance!

Answer 1

You can't OR combine a foreign table(manufacturers) condition with a parent table(items) condition directly through the JS SDK.

(More details about why here)

However you can create a function wrapping your query and use rpc to get it:

https://supabase.com/docs/reference/javascript/rpc