CODEWITHSUNDEEP
javascripttypescriptsupabasesupabase-databasesupabase-js
Yanik
Yanik

Reputation: 11

Supabase ilike filters on main and foreign table at the same time?

I'm new into Supabase. I have 2 tables, the main Table (Food) and a foreign table (brands) one food can only have one brand. I'm writing a function to get Foods by using a text. the text can be the brand or the product_name.

How can I filter them at the same time?

const {data, error} = await supabase
        .from('foods')
        .select(
            `id, 
     product_name, 
     brands(brand_name),
     barcode,
     serving_factor,
     units(unit),
     calories, 
     fat,
     saturated_fat,
     carbohydrates,
     sugar,
     protein,
     salt,
     image,
     vegan,
     lactose_free,
     gluten_free,
     source`
        ).FILTER_HERE?

I tried multiple things, but it wont work, so maybe you could help me?

I tried multiple things, but it wont work, so maybe you could help me?
My workaround would be two seperate requests, one for the Productname and one for the brands and then merge those two results. But I think there should be a solution to do this in one request?

It should be the equivalent to: select * from foods join brands on foods.brand = brands.id where brands.brand_name ilike '%BRANDNAME%'

Upvotes: 1

Views: 1583

Answers (2)

bk3
bk3

Reputation: 167

I tried to do the same thing with supabase, filtering using "OR" and ilike for a searchbar and I finally used an SQL function called with supabase.rpc()

but if you want to use "OR" and ilike only on the foreign table you can use { referencedTable: fieldName}

example:

const { data, error } = await this.supabase
    .from('foods')
    .select('*, brands(brand_name)')
    .or(`brand_name.ilike.%${brandName}%`, { referencedTable: 'brands' })

Upvotes: 0

AgainPsychoX
AgainPsychoX

Reputation: 1801

Have you tried using or? https://supabase.com/docs/reference/javascript/or It requires using raw PostgREST syntax, so looking it up at https://postgrest.org/en/v9.0/api.html#operators might be useful.

I never used the Supabase, but maybe following is what you need:

    const what = "Paprykarz";
    const {data, error} = await supabase
        .from('foods')
        .select(/* your select */)
        .or(`product_name.ilike."*${what}*",brand_name.ilike."*${what}*"`)

Please keep in mind there might be safety concern about using raw PostgREST filter string like this which might result in SQL injection, so try clean the input & test it properly. See https://github.com/orgs/supabase/discussions/3843 for some discussion. Someone should edit my answer to provide more insight how to keep it safe.

Upvotes: 1

Related Questions