CODEWITHSUNDEEP
supabasesupabase-databasesupabase-js
Muhammad Haekal
Muhammad Haekal

Reputation: 632

supabase ilike with multiple reference table

Is it possible to do filters or with multiple reference tables, like if we do it in raw postgresql query like below (just for example):

SELECT reports.*, report_categories.*, report_details.* FROM reports
LEFT JOIN report_categories on report_categories.id = reports.report_category_id
LEFT JOIN report_details on report_details.id = reports.report_detail
LEFT JOIN listings on listings.id = reports.listing_id
WHERE reports.ticket_no ILIKE '%somthing%' or listings.title ILIKE '%somthing%';

I Tried to achieved it using supabase, but always get below.

{
    "reportData": null,
    "error": {
        "code": "42703",
        "details": null,
        "hint": null,
        "message": "column reports.title does not exist"
    }
}

Here my code;

async function getReport(req) {
const { page, limit, search, type, sort, status } = req.query;

let query = supabase
  .from(tables.REPORTS)
  .select(
    `*,
report_category:report_category_id(id, title, type),
buyer:buyer_id(id, display_name, profile_picture), 
listing:listing_id(id, title, photos, thumbnails, store(id, display_name, profile_picture, toco_name, toco_profile_picture, toco_short_name)), 
store:store_id(id, display_name, profile_picture, toco_name, toco_profile_picture, toco_short_name)`,
    { count: "exact" }
  )
  .is("deleted_at", null);

if (status) {
  const splittedStatus = status.split(",");
  query = query.in("status", splittedStatus);
}

// ***** this line what I'm trying to achieve *****

if (search) {
  query = query.or(`ticket_no.ilike.'%${search}%',title.ilike.'%${search}%',toco_name.ilike.'%${search}%'`);
}

// *****************************

const limitInt = Number(limit) || 10;
const offset = (Number(page) - 1) * limitInt || 0;
const currentPage = Number(page) || 1;

query = query.order("created_at", {
  ascending: sort === "asc" ? true : false,
});

query = query.range(offset, offset + limitInt - 1);

const { data: reportData, error, count } = await query;

return reportData

Please anyone help me, I've been trying to achieve for many days, but no results. Thanks in advance

Upvotes: 1

Views: 649

Answers (1)

dshukertjr
dshukertjr

Reputation: 18670

The code below isn't tested, but you should be able to specify the columns by appending the table name in front of it like listing.title.ilike. Not sure which table toco_name belongs to, but if it's not on the reports table, then you would need to add the table name there too.

if (search) {
  query = query.or(`ticket_no.ilike.'%${search}%', listing.title.ilike.'%${search}%',toco_name.ilike.'%${search}%'`);
}

Upvotes: 0

Related Questions