多对多 SQL 查询，用于选择用某些单词标记的所有图像

发布于 2024-12-09 12:18:47 字数 746 浏览 0 评论 0原文

我在 Postgres 中有 2 个表：

CREATE TABLE "images" (
    "id" serial NOT NULL PRIMARY KEY,
    "title" varchar(300) NOT NULL,
    "relative_url" varchar(500) NOT NULL)

为了

CREATE TABLE "tags" (
    "id" serial NOT NULL PRIMARY KEY,
    "name" varchar(50) NOT NULL)

在图像和标签之间建立多对多关系，我还有另一个表：

CREATE TABLE "tags_image_relations" (
    "id" serial NOT NULL PRIMARY KEY,
    "tag_id" integer NOT NULL REFERENCES "tags" ("id") DEFERRABLE INITIALLY DEFERRED,
    "image_id" integer NOT NULL REFERENCES "images" ("id") DEFERRABLE INITIALLY DEFERRED)

现在我必须编写一个查询，例如“选择标有‘apple’和‘microsoft’的所有图像的relative_url和'google'“

对此最优化的查询是什么？

原文

I have 2 Tables in Postgres:

CREATE TABLE "images" (
    "id" serial NOT NULL PRIMARY KEY,
    "title" varchar(300) NOT NULL,
    "relative_url" varchar(500) NOT NULL)

and

CREATE TABLE "tags" (
    "id" serial NOT NULL PRIMARY KEY,
    "name" varchar(50) NOT NULL)

To establish many to many relationship between images and tags I have another table as:

CREATE TABLE "tags_image_relations" (
    "id" serial NOT NULL PRIMARY KEY,
    "tag_id" integer NOT NULL REFERENCES "tags" ("id") DEFERRABLE INITIALLY DEFERRED,
    "image_id" integer NOT NULL REFERENCES "images" ("id") DEFERRABLE INITIALLY DEFERRED)

Now I have to write a query like "select relative_url of all images tagged with 'apple' and 'microsoft' and 'google' "

What can the most optimized query for this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

飞烟轻若梦 2024-12-16 12:18:47

这是我编写的工作查询：

SELECT i.id, i.relative_url, count(*) as number_of_tags_matched
FROM   images i
    join tags_image_relations ti on i.id = ti.image_id
    join tags t on t.id = ti.tag_id
    where t.name in ('google','microsoft','apple')
    group by i.id having count(i.id) <= 3
    order by count(i.id)

该查询将首先显示与所有三个标签匹配的图像，然后显示与 3 个标签中至少 2 个标签匹配的图像，最后至少显示 1 个标签。

Here's the working query I wrote:

SELECT i.id, i.relative_url, count(*) as number_of_tags_matched
FROM   images i
    join tags_image_relations ti on i.id = ti.image_id
    join tags t on t.id = ti.tag_id
    where t.name in ('google','microsoft','apple')
    group by i.id having count(i.id) <= 3
    order by count(i.id)

This query will first show the images matching all three tags, then the images matching at least 2 of the 3 tags, finally at least 1 tag.

回复收藏 0 原文