S
Supabase•2w ago
Daniel Gomes-Sebastiao

Question about supabase-js client capabilities

Hey everyone, I am trying to crack the syntax for a query using the supabase-js client. I am trying to replicate this query: SELECT COUNT(*) as invoice_count FROM invoices i JOIN customers c ON i.customer_id = c.id WHERE c.name ILIKE '%delba%'; My attempts have got me to: 
const { count, error } = await supabase.from('invoices').select(
      `customers (
        name
      )
        `, { count: 'exact' })
      .ilike('customers.name', `%delba%`);
const { count, error } = await supabase.from('invoices').select(
      `customers (
        name
      )
        `, { count: 'exact' })
      .ilike('customers.name', `%delba%`);
The problem seems to be that the count is not factoring in the ilike condition at the end of the function. It always returns the full amount of rows in the invoice table, as if the ilike is not considered at all. Am I doing this right? There are a number of other ways to do it, e.g. running multiple queries, counting the results after the fact, etc... But I am trying to get this right purely with the JS client to keep things from sprawling in different styles and methods. How can we accomplish this with the js client?
3 Replies
garyaustin
garyaustin•2w ago
You might see what this says. https://supabase.com/docs/guides/api/sql-to-rest
SQL to REST API Translator | Supabase Docs
Translate SQL queries to HTTP requests and Supabase client code
Daniel Gomes-Sebastiao
Daniel Gomes-SebastiaoOP•2w ago
Hey @garyaustin . That tool was insightful, thanks! The query it made didn't work either because: `Use of aggregate functions is not allowed But I have a better understanding of PostgREST now. 👍 It seems a lot of people are leaning towards DB functions and RPC in place of handling the queries with Javascript. I am somewhat forced to do the same because I don't want to blend my queries between JS and DB functions. Thanks so much for the response, it helped!
garyaustin
garyaustin•2w ago
You have to turn on aggregate functions.

Did you find this page helpful?