I'm trying to generate a query that will handle tags matching in a database.
The simplified structure is
create table contacts (
id serial primary key,
name varchar
);
create table books (
id serial primary key,
name varchar
);
create table tags (
id serial primary key,
name varchar
);
create table taggings (
tag_id int,
tagged_id int,
tagged_type int -- points to the table this tag is tagging
);
What I want to now achieve is to find all items that are tagged with
the same set of tags. So it's an AND matching on a list of tags I
have.
I have two types of matching. One is within the same object type
(where both tagged objects are the same, say two books with the same
set of tags) and one that will find ANY object that's tagged with the
same tag (like book and contact)
Current query (for the same object type) I am using is the following,
for a list of 4 tags called summer, winter, spring and fall.
SELECT *
FROM contacts WHERE 4 = ( SELECT COUNT(*)
FROM tags, taggings
WHERE tags.id = taggings.tag_id
AND lower(tags.name) IN ( 'summer' , 'winter', 'spring', 'fall' )
AND taggings.tagged_type = 1
AND taggings.tagged_id = contacts.id);
The query to match all the objects tagged with a given set of tags is:
SELECT DISTINCT taggings.tagged_id, taggings.tagged_type
FROM taggings WHERE 4 = ( SELECT COUNT(*)
FROM tags, taggings as taggings2 WHERE tags.id = taggings2.tag_id
AND lower(tags.name) IN ( 'summer' , 'winter', 'spring', 'fall' )
AND taggings.tagged_type = taggings2.tagged_type
AND taggings.tagged_id = taggings2.tagged_id );
The idea in both is to see that I find the number of tags needed.
I've attached a script that will create the tables, insert some data
and run the queries to make it easy to try it.
Is there a way to simplify this query and make it more efficient?
Thanks!
Guy.
--
Family management on rails: http://www.famundo.com - coming soon!
My development related blog: http://devblog.famundo.com
drop table taggings;
drop table contacts;
drop table books;
drop table tags;
create table contacts (
id serial primary key,
name varchar
);
create table books (
id serial primary key,
name varchar
);
create table tags (
id serial primary key,
name varchar
);
create table taggings (
tag_id int,
tagged_id int,
tagged_type int -- points to the table this tag is tagging
);
insert into contacts (id,name) VALUES (1,'guy');
insert into contacts (id,name) VALUES (2,'michal');
insert into contacts (id,name) VALUES (3,'gal');
insert into contacts (id,name) VALUES (4,'noa');
insert into contacts (id,name) VALUES (5,'edo');
insert into books (id,name) VALUES (1,'B1');
insert into books (id,name) VALUES (2,'B2');
insert into books (id,name) VALUES (3,'B3');
insert into books (id,name) VALUES (4,'B4');
insert into books (id,name) VALUES (5,'B5');
insert into tags (id,name) values (1,'summer');
insert into tags (id,name) values (2,'winter');
insert into tags (id,name) values (3,'spring');
insert into tags (id,name) values (4,'fall');
insert into tags (id,name) values (5,'sea');
insert into tags (id,name) values (6,'beach');
insert into taggings (tag_id,tagged_id,tagged_type) values (1,1,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (2,1,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (3,1,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (4,1,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (1,2,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (2,2,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (3,2,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (4,2,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (5,2,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (1,3,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (4,3,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (1,4,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (2,4,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (3,4,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (4,4,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (1,2,2);
insert into taggings (tag_id,tagged_id,tagged_type) values (2,2,2);
insert into taggings (tag_id,tagged_id,tagged_type) values (3,2,2);
insert into taggings (tag_id,tagged_id,tagged_type) values (4,2,2);
insert into taggings (tag_id,tagged_id,tagged_type) values (1,1,2);
insert into taggings (tag_id,tagged_id,tagged_type) values (4,1,2);
-- Find all items tagged with the same set of tags
SELECT DISTINCT taggings.tagged_id, taggings.tagged_type
FROM taggings
WHERE 4=
( SELECT COUNT(*)
FROM tags, taggings as taggings2
WHERE tags.id = taggings2.tag_id
AND lower(tags.name) IN ( 'summer' , 'winter', 'spring', 'fall' )
AND taggings.tagged_type = taggings2.tagged_type
AND taggings.tagged_id =
