Thread: A better AND query?

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

>  tagged_type int -- points to the table this tag is tagging

My head exploded right about here.  Is the schema written in stone, or
can it change?

What is the use case for this schema?  What's it for?  What is a "tag"
about?

Best Regards,
        Wayne Conrad

The schema can change, but I rather not.

The use case is a web app where you can tag items with tags
(many-2-many). There are multiple items you can tag: contacts,
schedules, lists, etc... And then you can search and categorize by
tags. The standard for this if you look aroung the web is to retrieve
the tagged records with any of the tags you select. Effectively an OR
query.

What I'm trying to do is search for items matching multiple tags at
the same time - and AND query. So that I can bring up all contacts
that are tagged with friends and movie-lovers.

Hope that clears it up a bit...

Guy.

On 5/9/06, Wayne Conrad <wconrad@yagni.com> wrote:
> >  tagged_type int -- points to the table this tag is tagging
>
> My head exploded right about here.  Is the schema written in stone, or
> can it change?
>
> What is the use case for this schema?  What's it for?  What is a "tag"
> about?
>
> Best Regards,
>         Wayne Conrad
>


--
Family management on rails: http://www.famundo.com - coming soon!
My development related blog: http://devblog.famundo.com