OR query for relationship table

[SherifMega]

i need to know how to apply (or like) for 2 columns in relationship

i'm fetching jobs with it's customers i need to search by or like within first_name and last_name of customers

  let query = supabase
    .from("jobs")
    .select(
      `
        *, 
        custFilter:customers!inner(),
        customers:customers(first_name, last_name)
      `,
      { count: "exact" }
    )
    .range(offset, offset + pageSize - 1);

  // Add search filter
  if (searchQuery) {
    const isNumber = !isNaN(Number(searchQuery));
    if (isNumber) {
      //this is because like operator not working with integer columns
      query = query.filter("id", "eq", Number(searchQuery));
    } else {
      query = query
      .or(`custFilter.first_name.ilike%${searchQuery}%,custFilter.last_name.ilike%${searchQuery}%`)
      .or(
        `custFilter.not.is.null`
      );
    }
  }

error

{
    "code": "PGRST100",
    "details": "unexpected \"f\" expecting \"not\" or operator (eq, gt, ...)",
    "hint": null,
    "message": "\"failed to parse logic tree ((custFilter.first_name.ilike%rob%,custFilter.last_name.ilike%rob%))\" (line 1, column 15)"
}

[SherifMega]

@laurenceisla pls can you support ?

[laurenceisla]

Wolfgang's response below tackles everything you need to make the query work.

[laurenceisla]

Just to add, in supabase-js follow the example given for "Use or on referenced tables".

[SherifMega]

thanks ❤️

[wolfgangwalther]

custFilter.first_name.ilike%rob%

Two problems with that:

• % is problematic because of percent encoding in URLs. use * instead as mentioned in the docs.
• once inside or you need to replace = with .. See examples in the docs.

Thus: custFilter.first_name.ilike.*rob* should work better.

[wolfgangwalther]

Ah, and a third problem: or with embedding doesn't work like that.

See the docs.

Instead of or=(custFilter.first_name.ilike.*rob*,... it needs to be something along the lines of custFilter.or=(first_name.ilike.*rob*,...).

[SherifMega]

thanks a lot ❤️ it works fine
this is updated typescript code based on docs you need to pass { foreignTable: "custFilter" } as separated params for or

  let query = supabase
    .from("jobs")
    .select(
      `
        *,
        custFilter:customers(first_name,last_name),
        customers:customers(first_name, last_name)
      `,
      { count: "exact" },
    )
    .range(offset, offset + pageSize - 1);

  // Add search filter
  if (searchQuery) {
    const isNumber = !isNaN(Number(searchQuery));
    if (isNumber) {
      query = query.or(`id.eq.${Number(searchQuery)}`);
    } else {
      query = query.or(
        `first_name.ilike.*${searchQuery}*,last_name.ilike.*${searchQuery}*`,
        { foreignTable: "custFilter" },
      ).or('custFilter.not.is.null');
    }
  }