Best way to "build" supabase queries

[andregoldstein]

Hi there!

Seeing as Supabase API querying is chaining operations eg: .select().filter(), what would be the best way of building out a query depending on certain values.

For example, if there is a search query value (from an input) add a filter.

I'm sure I'm missing the easiest/best approach here?

Thanks!

[steve-chavez]

Hey @andregoldstein,

Do you mean adding an input to a function? Like the below?

let input = 'fat & rat'; // can come from user input
const { data, error } = supabase
  .from(table)
  .select()
  .fts('fat cats ate fat rats', input)

[andregoldstein]

Thanks, not completely sure I follow, though seems like fts may be useful for full text search. Is fts short for full text search anyway? May have to try that out!

[inian]

Hi @andregoldstein,
I just found this out as well, but looks like the Supabase query is only executed when you call await or .then. So you should be able to do something like this

const a = supabase.from("messages");

if (input < 100) {
  a.insert([
    {
      message: "sdf11",
      user_id: "8d0fd2b3-9ca7-4d9e-a95f-9e13dded323e",
      channel_id: 2,
    },
  ]);
} else {
  a.insert([
    {
      message: "sdf22",
      user_id: "8d0fd2b3-9ca7-4d9e-a95f-9e13dded323e",
      channel_id: 2,
    },
  ]);
}

if (limit) {
  a.limit(10)
}
// fire off the query
await a;

[kiwicopple]

Hey @andregoldstein can you please share a code snippet of what you're trying to achieve?

heavily filter a list (table) of results so sometimes things will need filtering and sometimes not.

Sounds like you want to use a debounce function around the request (like this) to make sure it's not triggering a huge number of database requests?

perhaps that match can be reconstructed for each query and run again?

yes, any of the filters can can really. It might be something like:

import { createClient } from '@supabase/supabase-js'

const supabase = createClient("https://xyzcompany.supabase.co", "public-anon-key") 


// Call this function on input change
// You should add debounce if you don't want to call it too often!
const onFilter = async (filterText) => {
  const { data, error } = await supabase
    .from('cities')
    .select('name, country_id')
    .ilike('name', `%${filterText}%`)
  return data
}

[andregoldstein]

Hey thanks @kiwicopple for the reply and sorry for late response.

Sorry for being unclear, I'm more wondering how to craft a query with multiple parameters. As an example on a page of products I'd have a query input, a min price input, max price, expired or not dropdown.

Sometimes these inputs will be empty "" so I need to somehow "craft" a full query in the Supabase client to allow for these values as they get updated.

If using a standard REST Api I may have an object which I update with the search parameters before firing off the request. Similarly in GraphQL I may structure an object to send through as variables as part of the request but I'm less sure on how to do this with Supabase as it uses chained methods.

For example I couldn't pass the following in (as sometimes the query will be empty)

let query = ""
...
await supabase.from('products').select('*').ilike('name', '%query%')

So that ilike, or more realistically multiple filtering selectors, would need to either accept empty values or be only called conditionally if the inputs have been used.

Ideally it would be cool to see an example of a query that uses multiple different fields. Below would be a pseudo-layout of a data page in my app:

[search input] [min_price] [max_price] [expired dropdown]

[...results]

As these are typed in I'd need my Supabase query to reflect them.

I can provide examples of how I'd do this with REST or GraphQL if that helps make it clearer? Sorry if it's not!

Thanks again

[silentworks]

@andregoldstein this should be possible since the query builder always returns itself. I don't have a project I can experiment with at the moment, but if you have one just share the repo with me along with the SQL file to set up the tables with data and I will update it to match your requirements.

[andregoldstein]

Thanks very much that's kind of you, I'll try and set something up to replicate this, currently I am just finding my feet to see if this approach could replicate my existing setup.

[zlever01]

Here's a sample of how I achieved a supabase query with multiple parameters with help from here #3080 . The queryKeys are coming from react-query.

export async function getProducts({queryKey} ) {
    const minValue = queryKey[1].min
    const maxValue = queryKey[2].max
    const sortValue = queryKey[3].sort.value    // ['date', false]
    const colorValue = queryKey[4].color.map(value => value.value)
    const brandValue = queryKey[5].brand.value
    const searchValue = queryKey[7].search

    const filters = []
   
   if (minValue === '') {
        filters.push(['gte', 'weight', 120])
    } else {
        filters.push(['gte', 'weight', Number(minValue)])
    }

    if (maxValue === '') {
        filters.push(['lte', 'weight', 190])
    } else {
        filters.push(['lte', 'weight', Number(maxValue)])
    }
  
    if (colorValue.length !== 0) {
        filters.push(['or', [colorValue.toString()]])
     }

    if (brandValue !== 0) {
        filters.push(['eq', 'brand', brandValue])
    }

    if (searchValue.length !== 0) {
        filters.push(['textSearch', 'fts', searchValue, {
            type: 'websearch',
            config: 'english'
        }])
    }

    const {data, error} = await filters
        .reduce(
            (acc, [filter, ...args]) => {
                return acc[filter](...args)
            },
            supabase
                .from('products')
                .select('*', {count: "exact"})
                .order(sortValue[0], {ascending: sortValue[1]})
        )

return {
        data
    }
}