ra-supabase中ReferenceInput嵌套AutocompleteInput搜索异常求助

问题 #

在ra-supabase中使用AutocompleteInput关联currency表和country表（country表含currency_id字段）时遇到搜索问题。

当前代码：

<ReferenceInput source="currency_id" reference="currency">
  <AutocompleteInput name="currency_id" optionText={"code"} />
</ReferenceInput>

发送给Supabase的请求：

http://localhost:54321/rest/v1/currency?order=id.desc&offset=0&limit=25&q=eq.ddf

返回400错误：

{"code":"42703","details":null,"hint":null,"message":"column currency.q does not exist"}

尝试使用AutocompleteInput的filterToQuery解决了列不存在的问题，但请求仍使用eq匹配，无法改为like匹配，寻求解决方法。

表结构SQL #

Currency表

create table "public"."currency" (
    "id" character varying(255) not null default uuid_generate_v4(),
    "name" character varying(255) not null,
    "code" character(3) not null,
    "created_at" timestamp with time zone not null default CURRENT_TIMESTAMP,
    "updated_at" timestamp with time zone not null default CURRENT_TIMESTAMP
);

alter table "public"."currency" enable row level security;
alter table "public"."currency" add constraint "idx_17323_primary" PRIMARY KEY using index "idx_17323_primary";

Country表

create table "public"."country" (
    "id" character varying(255) not null default uuid_generate_v4(),
    "currency_id" character varying(255) not null,
    "name" character varying(255) not null,
    "code_2" character(2) not null,
    "created_at" timestamp with time zone not null default CURRENT_TIMESTAMP,
    "updated_at" timestamp with time zone not null default CURRENT_TIMESTAMP
);

alter table "public"."country" enable row level security;
CREATE INDEX idx_17316_fk_9307401d490f6b38540cc3c368b ON public.country USING btree (currency_id);
alter table "public"."country" add constraint "idx_17316_primary" PRIMARY KEY using index "idx_17316_primary";
alter table "public"."country" add constraint "country_currency_id_fkey" FOREIGN KEY (currency_id) REFERENCES currency(id) not valid;

alter table "public"."country" validate constraint "country_currency_id_fkey";

解决方案 #

要实现基于模糊匹配的搜索，需通过filterToQuery属性自定义查询参数，遵循Supabase REST API的过滤语法：

1. 修改AutocompleteInput的filterToQuery，将搜索关键词转换为针对code字段的ilike（不区分大小写）或like（区分大小写）查询：

<ReferenceInput source="currency_id" reference="currency">
  <AutocompleteInput 
    name="currency_id" 
    optionText={"code"}
    filterToQuery={(searchText) => ({
      code: `ilike.%${searchText}%`
    })}
  />
</ReferenceInput>

2. 调整后发送的请求会变为：

http://localhost:54321/rest/v1/currency?order=id.desc&offset=0&limit=25&code=ilike.%ddf%

Supabase会正确匹配currency表的code字段，执行模糊搜索。

说明 #

• ilike是PostgreSQL的不区分大小写模糊匹配，适合多数通用搜索场景；若需要区分大小写，替换为like即可。
• 必须用%包裹搜索关键词，这是SQL模糊匹配的语法要求，Supabase REST API会直接解析该格式。

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