如何在Supabase中实现多关联表OR条件的ILIKE过滤？

报错column reports.title does not exist的核心原因是：Supabase的or过滤器默认会在主表（此处为reports）中查找字段，但你要匹配的title属于关联的listing表，toco_name属于关联的store/listing.store表，必须明确指定关联表别名+字段名的完整路径，否则会被解析为主表字段，自然找不到对应列。

修改or条件的写法，通过关联表的别名精准定位字段，同时修复SQL注入风险：

1. 主表reports的ticket_no可直接使用
2. 关联表listing的title需写成listing.title.ilike.'%xxx%'
3. 关联表store的toco_name需写成store.toco_name.ilike.'%xxx%'
4. 若需要匹配listing关联的嵌套store里的toco_name，补充listing.store.toco_name.ilike.'%xxx%'
5. 必须对搜索关键词做转义或使用参数化查询，避免SQL注入

修改后的核心代码 #

if (search) {
  // 转义单引号防止SQL注入
  const safeSearch = `%${search.replace(/'/g, "''")}%`;
  query = query.or(
    `ticket_no.ilike.'${safeSearch}'`,
    `listing.title.ilike.'${safeSearch}'`,
    `store.toco_name.ilike.'${safeSearch}'`,
    `listing.store.toco_name.ilike.'${safeSearch}'`
  );
}

完整代码示例 #

替换原搜索逻辑后的完整函数：

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);
}

// 修复后的搜索过滤逻辑
if (search) {
  const safeSearch = `%${search.replace(/'/g, "''")}%`;
  query = query.or(
    `ticket_no.ilike.'${safeSearch}'`,
    `listing.title.ilike.'${safeSearch}'`,
    `store.toco_name.ilike.'${safeSearch}'`,
    `listing.store.toco_name.ilike.'${safeSearch}'`
  );
}

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
}

内容的提问来源于stack exchange，提问作者Muhammad Haekal