我正在为一个项目使用Go+GORM。我有一个DB结构,看起来像这样:
CREATE TABLE IF NOT EXISTS public.keywords (
keyword text NOT NULL,
PRIMARY KEY(keyword)
);
CREATE TABLE IF NOT EXISTS public.people (
id bigint DEFAULT nextval('people_id_seq'::regclass) NOT NULL,
created_at timestamp with time zone,
updated_at timestamp with time zone,
deleted_at timestamp with time zone,
name text,
PRIMARY KEY(id)
);
CREATE TABLE IF NOT EXISTS public.photos (
id text NOT NULL,
created_at timestamp with time zone,
updated_at timestamp with time zone,
deleted_at timestamp with time zone,
file_name text,
file_path text,
metadata_hash text,
lens_make text,
lens_model text,
lens_id text,
make text,
model text,
caption text,
title text,
creation_date_time timestamp with time zone,
width bigint,
height bigint,
latitude numeric,
longitude numeric,
ip_tc_digest text,
"year" text,
PRIMARY KEY(id)
);
CREATE TABLE IF NOT EXISTS public.photos_keywords (
photo_id text NOT NULL,
keyword_keyword text NOT NULL,
PRIMARY KEY(keyword_keyword)
);
ALTER TABLE IF EXISTS public.photos_keywords
ADD CONSTRAINT fk_photos_keywords_photo
FOREIGN KEY (photo_id)
REFERENCES public.photos (id);
ALTER TABLE IF EXISTS public.photos_keywords
ADD CONSTRAINT fk_photos_keywords_keyword
FOREIGN KEY (keyword_keyword)
REFERENCES public.keywords (keyword);
CREATE TABLE IF NOT EXISTS public.photos_people (
photo_id text NOT NULL,
person_id bigint NOT NULL,
PRIMARY KEY(person_id)
);
ALTER TABLE IF EXISTS public.photos_people
ADD CONSTRAINT fk_photos_people_person
FOREIGN KEY (person_id)
REFERENCES public.people (id);
ALTER TABLE IF EXISTS public.photos_people
ADD CONSTRAINT fk_photos_people_photo
FOREIGN KEY (photo_id)
REFERENCES public.photos (id);
我有以下查询来检索按人筛选的照片,每行都有关键字和链接的人聚合到一个数组中。
WITH photo_details AS (
SELECT
p.id
, p.file_name
, p.file_path
, p.lens_make
, p.lens_model
, p.lens_id
, p.make
, p.model
, p.caption
, p.title
, p.creation_date_time
, p.width
, p.height
, p.latitude
, p.longitude
, p.year
, json_agg(DISTINCT pk.keyword_keyword) FILTER (WHERE pk.keyword_keyword IS NOT NULL) AS keywords
, json_agg(DISTINCT jsonb_build_object('id', pp.person_id, 'name', pe.name)) FILTER (WHERE pp.person_id IS NOT NULL AND pe.name IS NOT NULL
AS people
FROM
photos p
LEFT JOIN photos_keywords pk ON pk.photo_id = p.id
LEFT JOIN photos_people pp ON pp.photo_id = p.id
LEFT JOIN people pe ON pe.id = pp.person_id
WHERE
p.deleted_at IS NULL
AND (
1=1
AND (
(ARRAY[339] IS NULL) OR
(pp.person_id = ANY(ARRAY[339]))
)
)
GROUP BY
p.id
ORDER BY
p.creation_date_time ASC
OFFSET 0 LIMIT 25
)
SELECT
id
, file_name
, file_path
, lens_make
, lens_model
, lens_id
, make
, model
, caption
, title
, creation_date_time
, width
, height
, latitude
, longitude
, year
, keywords
, people
FROM
photo_details
ORDER BY
creation_date_time, CONCAT(title, file_name)
WHERE子句中人员筛选器的值用以下代码填充:
if len(trimmedPeople) > 0 {
q := strings.Join(slices.Map(trimmedPeople, func(input uint, index int) string {
return "?"
}), ", ")
s.WriteString(`
AND (
ARRAY[` + q + `] IS NULL OR
pp.person_id = ANY(ARRAY[` + q + `])
)
`)
for _, personID := range trimmedPeople {
params = append(params, personID)
}
for _, personID := range trimmedPeople {
params = append(params, personID)
}
}
有一次,我试图传递变量
trimmedPeople
直接作为查询参数,但这会导致过滤器看起来像:
ARRAY[(339)]
这是错误的。问题是我看到了这个错误:
time=2025-01-10T10:19:46.612-06:00 level=ERROR msg="could not load photos in GetSearchPhotos" version=development error="could not query for phot
in searchPhotos: ERROR: operator does not exist: bigint = text (SQLSTATE 42883)"
上述查询可以在Postgres中直接执行,没有问题。GORM不支持数组参数吗?
