searching by tags

[Jasper Bryant-Greene]

I have three tables: `photos`, `tags` and `tags_photos`.

The `photos` table contains a unique ID and a bunch of other stuff, the
`tags` table contains a unique ID and a tag name, and the `tags_photos`
contains the photo ID and the tag ID for an association between a tag
and a photo.

In other words, any photo may have many tags and any tag may have many
photos.

This is great for filtering photos based on tag, but I'm now trying to
allow users to search photos on multiple tags. I want users to be able
to enter a list of tags and either search for photos with "all these
tags" or "any of these tags".

I tried the query "SELECT a.* FROM photos AS a, tags_photos AS b WHERE
b.photo=a.id AND b.tag IN ($tags)" where $tags is a comma-separated
list of the tag IDs from the tag names the user entered, but of course
it returns photos multiple times if they have more than one of the
entered tags.

Any ideas?

[Bill Karwin]

Jasper Bryant-Greene wrote:

I want users to be able
to enter a list of tags and either search for photos with "all these
tags" or "any of these tags".
Using the IN predicate gives you the latter case, but not the former.
I tried the query "SELECT a.* FROM photos AS a, tags_photos AS b WHERE
b.photo=a.id AND b.tag IN ($tags)" where $tags is a comma-separated
list of the tag IDs from the tag names the user entered, but of course
it returns photos multiple times if they have more than one of the
entered tags.

Here are two possible solutions:

1. Using a DISTINCT query modifier:

SELECT DISTINCT a.*
FROM photos AS a, tags_photos AS b
WHERE b.photo = a.id AND b.tag in ($tags)

2. Using a subquery:

SELECT a.*
FROM photos AS a
WHERE a.id IN (
SELECT b.photo
FROM tags_photos AS b
WHERE b.tag IN ($tags))

Regards,
Bill K.

[Jasper Bryant-Greene]

Thanks Bill, the DISTINCT modifier worked perfectly for the "any" case.
Does anyone have any ideas for how to implement the "all" case?

I've been playing around but can't seem to get on the right track.

[Bill Karwin]

Jasper Bryant-Greene wrote:

Thanks Bill, the DISTINCT modifier worked perfectly for the "any" case.
Does anyone have any ideas for how to implement the "all" case?

This one is a little more tricky. I don't think you can avoid using a
subquery here.

SELECT DISTINCT a.*
FROM photos AS a, tags_photos AS b
WHERE b.photo = a.id AND b.tag in ( $tags )
AND NOT EXISTS (
SELECT 1
FROM tags AS t LEFT OUTER JOIN tags_photos AS b2
ON (t.id = b2.tag AND b2.photo = a.id)
WHERE t.id IN ( $tags ) AND b2.tag IS NULL)

Regards,
Bill K.

[Jasper Bryant-Greene]

Hi Bill

Thanks for that. I get the following error though:

You have an error in your SQL syntax. Check the manual that corresponds
to your MySQL server version for the right syntax to use near 'EXISTS (
SELECT 1 FROM tags AS t LEFT OUTER JOIN tags_photos AS

Query: SELECT DISTINCT a.* FROM photos AS a, tags_photos AS b WHERE
b.photo=a.id AND b.tag IN (1, 3) AND NOT EXISTS ( SELECT 1 FROM tags AS
t LEFT OUTER JOIN tags_photos AS b2 ON (t.id = b2.tag AND b2.photo =
a.id) WHERE t.id IN (1, 3) AND b2.tag IS NULL )

mysqld Ver 4.0.24 for pc-linux-gnu on i686 (Gentoo Linux
mysql-4.0.24-r1)

Cheers,
Jasper

[Felix Geerinckx]

On 14/06/2005, Bill Karwin wrote:

Jasper Bryant-Greene wrote:

Thanks Bill, the DISTINCT modifier worked perfectly for the "any"
case. Does anyone have any ideas for how to implement the "all"
case?

This one is a little more tricky. I don't think you can avoid using
a subquery here.

Wouldn't this work?

USE test;
DROP TABLE IF EXISTS photos;
CREATE TABLE photos (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(100)
);

INSERT INTO photos (id, name) VALUES
(1, 'My favourite pet'), (2, 'My house'), (3, "Girlfriend 's cat");

DROP TABLE IF EXISTS tags;
CREATE TABLE tags (
tagname VARCHAR(10) NOT NULL,
photo_id INT NOT NULL,
PRIMARY KEY (tagname, photo_id)
);

INSERT INTO tags (tagname, photo_id) VALUES
('pets', 1), ('pets', 3), ('dogs', 1), ('cats', 3), ('buildings', 2);

SELECT
photos.id,
photos.name
FROM photos
JOIN tags ON tags.photo_id = photos.id
WHERE
tags.tagname IN ('pets', 'dogs')
GROUP BY photos.id
HAVING COUNT(*) = 2; # 2 = number of tags
--
felix

[Bill Karwin]

Jasper Bryant-Greene wrote:

mysqld Ver 4.0.24

Okay, that explains it. Subqueries were not implemented in MySQL 4.0.x.
They were added in MySQL 4.1. You'll have to use a solution like
Felix's, adapted to your many-to-many table `tags_photos`.

Regards,
Bill K.

[Jasper Bryant-Greene]

Thanks Felix and Bill, I've sorted it out using a solution based on
Felix's but adapted slightly for my many-to-many table.

Once I know whether I'll be using my own server or hosting this
application somewhere, I may well upgrade to MySQL 4.1 anyway.