开发者

Many to Many SQL Query for selecting all Images Tagged with certain words

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) N开发者_Go百科OT 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?


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.


You'd join images to tags_image_relations, then tags_image_relations to tags, then filter WHERE the name field is IN a list of tag names desired. It's the simplest, most obvious, and cleanest?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜