A better AND query?

Поиск
Список
Период
Сортировка
От Just Someone
Тема A better AND query?
Дата
Msg-id 36932f270605091126l1e07b1f9wf55d17a10bf8b36e@mail.gmail.com
обсуждение исходный текст
Ответы Re: A better AND query?  (Wayne Conrad <wconrad@yagni.com>)
Список pgsql-general
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

Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: [PERFORM] Arguments Pro/Contra Software Raid
Следующее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: [PERFORM] Arguments Pro/Contra Software Raid