Search by tag - narrowing the results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Search by tag - narrowing the results

Author

Topic

GazNewt
Starting Member

14 Posts

Posted - 2007-01-13 : 09:27:26

Hi

I am trying to implement a simple tag search mechanism for photos in my database. Photos can be given multiple tags.

Here's a summary of what I'm doing so far. There are three tables; one for the photos, one for all the individual tags in the system and one to match tags against photos.


create table photo (id bigint, path varchar(100))

create table tags (id bigint, tag varchar(25))

create table phototags( id bigint, photoid bigint, tagid bigint)

In the stored procedure to search for photos with tags I put all the tag ids (from the tags in the search string) into a temporary table. Then I join the photo table onto the phototags table where the phototags.tagid is IN the temporary table.

This gets back all photos containing ANY of the tags in the search string.

This isn't what I want. I want the select statement to only find photos which are tagged against ALL the tags in the search string.

Here's what the stored procedure does :


create table #tagids( id bigint)
-- note: I've left out the code which
-- populates this table, but it contains
-- all tag ids from the search string

select distinct
	photo.id,
	photo.path
from
	photo
join phototags on phototags.photoid = photo.id
where 
	phototags.tagid in (select id from #tagids)

To illustrate the problem, if I have


photo	
    id  path
    --  ----
    1   squirrel.jpg
    2   birds feeding.jpg

tags	
    id  tag
    --	--- 
    1   wet squirrel 
    2   seeds 
    3   bird table
    4   birds
	
phototags
    id  photoid  tagid
    1   1        1
    2   1        2
    3   2        2
    4   2        3
    4   2        4

Squirrel.jpg has the following tags
"wet squirrel" seeds

birds feeding.jpg has the following tags
seeds "bird table" birds

A search for ("wet squirrel" seeds) will bring back one picture, id 1. Correctly

A search for ("bird table" birds) will bring back 1 picture, id 2. Correctly

A search for (birds seeds) will bring back both pictures, correctly

However, a search for (seeds "bird table" birds) will bring back both pictures because each of these photos contains at least one of the given tags. I actually want it to to return only "birds feeding.jpg", since it is the only image in the database that contains ALL the tags in the search string

Have I described this ok? Can anyone help?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-13 : 09:48:28

[code]SELECT p.ID,
p.Path
FROM Photo AS p
WHERE p.ID IN (SELECT pt.PhotoID FROM Tags AS t INNER JOIN PhotoTags AS pt ON pt.TagID = t.ID WHERE t.Tag LIKE '%' + @Search + '%')[/code]

Peter Larsson
Helsingborg, Sweden

GazNewt
Starting Member

14 Posts

Posted - 2007-01-13 : 09:58:54

quote:

Originally posted by Peso

SELECT	p.ID,
	p.Path
FROM	Photo AS p
WHERE	p.ID IN (SELECT pt.PhotoID FROM Tags AS t INNER JOIN PhotoTags AS pt ON pt.TagID = t.ID WHERE t.Tag LIKE '%' + @Search + '%')

Peter Larsson
Helsingborg, Sweden

Thanks Peso. I'm sure this will work but it seems like a bit of overkill to use LIKE

I really wanted a solution that only uses the id fields as these are indexed fields. The tags.Tag varchar column is not indexed and there could be thousands of them - so it will be slow when the database grows

Being a sql novice I am probably wrong, is your suggestion the standard way of solving my problem? For example, would flickr or google do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-13 : 12:22:04

[code]
CREATE VIEW dbo.vwPhotos

SELECT p.ID AS PhotoID,
p.Path AS PhotoPath,
t.ID AS TagID,
t.Tag
FROM Photo AS p
INNER JOIN PhotoTags AS pt ON pt.PhotoID = p.ID
INNER JOIN Tags AS t ON t.ID = pt.TagID
GO

select photoid,
photopath
from vwPhotos
where tag = 'seeds'
group by photoid,
photopath
order by count(*) desc,
photoid desc[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources