How to Use AND Operator with OR Condition in Supabase Query?

Question

I'm working on a query in Supabase where I need to select specific columns from a table based on multiple conditions. Specifically, I need to select columns a, b, and c from a table where column e is equal to a specific value (e = e) and at least one of the columns (a, b, or c) is equal to another value. Let's call it g.

SELECT a,b,c

FROM table

WHERE e = e

AND (a = g OR b = g OR c = g);

I'm hoping for something similar to the below:

supabase
    .from('table')
    .select('a, b, c')
    .eq('e', e)
    .then(({ data, error }) => {
        if (error) {
            console.error('Error fetching data:', error.message);
        } else {
            console.log('Data:', data);
        }
    });

But additionally with the AND operator query above.

However, this only handles the condition where e = e. How can I incorporate the additional condition where at least one of a, b, or c is equal to g using the AND operator in Supabase?

I've tried using the .or() method within the .eq() method, but I haven't been successful. Can anyone guide me on how to structure this query properly?

Thank you.

Answer 1

According documentation, you can chain filter conditions and by default they will be chained as AND. And here is syntax for .or().

Putting it all together:

supabase
    .from('table')
    .select('a, b, c')
    .eq('e', e)
    .or('a.eq.g, b.eq.g, c.eq.g')
    .then(({ data, error }) => {
        if (error) {
            console.error('Error fetching data:', error.message);
        } else {
            console.log('Data:', data);
        }
    });

Answer 2

To use the AND operator with an OR condition in a Supabase query, you can structure your query using parentheses to ensure proper grouping of conditions,

You should need to follow below syntax...

SELECT * FROM your_table
WHERE (condition1 AND (condition2 OR condition3));

Or in code you can modify like this,

supabase
  .from('table')
  .select('a, b, c')
  .eq('e', e)
  .or(`a.eq.${g}`, `b.eq.${g}`, `c.eq.${g}`)
  .then(({ data, error }) => {
    if (error) {
      console.error('Error fetching data:', error.message);
    } else {
      console.log('Data:', data);
    }
  });

In above code i add

.or(a.eq.${g}, b.eq.${g}, c.eq.${g})

It checks if at least one of columns a, b, or c is equal to the value g.

That's it, i hope I'm able to solve your query.