SupabaseS
Supabase•4y ago
Beteer

checking if value in column (defined as array) contains given substring

i have users table with column id, name, skills (defined as array).
for example
{
  "id" : 1,
  "name" : "John",
  "skills" : ["JavaScript", "Java", "Pascal", "C++"]
},
{
  "id" : 2,
  "name" : "Michael",
  "skills" : ["JavaScript", "Pascal", "C++"]
}

but when i type "Java" it only returns row with id = 1, i want it also to return the second row, because the word "JavaScript" also contains "java". Here's the model i made. 

getAllUsers: (objParams) =>
    new Promise((resolve, reject) => {
      let query = supabase.from("user").select("*");

      if (objParams.search.length > 0) {
        query = query.contains("skill", objParams.search);
      }

      query
        .order(objParams.column, { ascending: objParams.order })
        .range(objParams.offset, objParams.offset + objParams.limit - 1)
        .then((result) => {
          if (!result.error) {
            resolve(result);
          } else {
            reject(result);
          }
        });
    }),


objParams is an object contains some values for filtering: page, limit, search for keyword to search, order type (asc or desc).

if it was just a text i usually use ilike for that. for an array is there any possible ways to achieve that using .contains() ?
Was this page helpful?