Сопоставление SQL многие-ко-многим

Я внедряю систему тегов для веб-сайта. Существует несколько тегов на объект и несколько объектов на тег. Это достигается путем ведения таблицы с двумя значениями на запись, по одному для идентификаторов объекта и тега.

Я ищу, чтобы написать запрос, чтобы найти объекты, которые соответствуют заданному набору тегов. Предположим, у меня были следующие данные (в формате [object] - > [tags]*) 

apple -> fruit red food
banana -> fruit yellow food
cheese -> yellow food
firetruck -> vehicle red

Если я хочу соответствовать (красный), я должен получить яблоко и пожарную машину. Если я хочу соответствовать (фрукты, еда) я должен получить (яблоко, банан).

Как написать SQL-запрос do do what I want?

@Jeremy Ruten,

Спасибо за ответ. Используемая нотация была использована для получения некоторых образцов данных - в моей базе данных есть таблица с 1 идентификатором объекта и 1 тегом на запись.

Во-вторых, моя проблема заключается в том, что мне нужно получить все объекты, соответствующие всем тегам. Подставляя ваше или вместо и так: 
SELECT object WHERE tag = 'fruit' AND tag = 'food';

Не дает никаких результатов при запуске.

6 3
sql tagging many-to-many

6 ответов:

Дано:

  • таблица объектов (идентификатор первичного ключа)
  • таблица objecttags (внешние ключи objectId, tagid)

  • Таблица тегов (идентификатор первичного ключа) 

    SELECT distinct o.*
  from object o join objecttags ot on o.Id = ot.objectid
                join tags t on ot.tagid = t.id
 where t.Name = 'fruit' or t.name = 'food';

Это кажется обратным, так как вы хотите и, но проблема в том, что 2 метки не находятся в одной строке, и поэтому, и ничего не дает, так как 1 единственная строка не может быть одновременно фруктом и едой. Этот запрос обычно дает дубликаты, потому что вы получите 1 строку каждого объекта на тег.

Если вы хотите действительно сделайте и в этом случае, вам понадобится group by, и having count = <number of ors> в вашем запросе, например.

  SELECT distinct o.name, count(*) as count
    from object o join objecttags ot on o.Id = ot.objectid
                  join tags t on ot.tagid = t.id
   where t.Name = 'fruit' or t.name = 'food'
group by o.name
  having count = 2;
4

О боже, я, возможно, неправильно истолковал ваш первоначальный комментарий.

Самый простой способ сделать это в SQL-иметь три таблицы: 

1) Tags ( tag_id, name )
2) Objects (whatever that is)
3) Object_Tag( tag_id, object_id )

Тогда вы можете задать практически любой вопрос о данных быстро, легко и эффективно (при условии, что вы индексируете соответствующим образом). Если вы хотите получить фантазии, вы можете позволить теги нескольких слов, тоже (есть элегантный способ, и менее элегантный способ, я могу придумать).

Я предполагаю, что это то, что у вас есть, поэтому этот SQL ниже будет работа:

Буквальный Способ: 

    SELECT obj 
      FROM object
     WHERE EXISTS( SELECT * 
                     FROM tags 
                    WHERE tag = 'fruit' 
                      AND oid = object_id ) 
       AND EXISTS( SELECT * 
                     FROM tags 
                    WHERE tag = 'Apple'
                      AND oid = object_id )

Есть и другие способы сделать это, например:

SELECT oid
  FROM tags
 WHERE tag = 'Apple'
INTERSECT
SELECT oid
  FROM tags
 WHERE tag = 'Fruit'
4

@Kyle: ваш запрос должен быть больше похож: 

SELECT object WHERE tag IN ('fruit', 'food');

Ваш запрос искал строки, в которых тег был одновременно фруктом и едой, что невозможно, поскольку поле может иметь только одно значение, а не оба одновременно.

0

Объедините предложение Стива м. с предложением Джереми вы получите одну запись с тем, что вы ищете: 

select object
from tblTags
where tag = @firstMatch
and (
       @secondMatch is null 
       or 
       (object in (select object from tblTags where tag = @secondMatch)
     )
Теперь, это не очень хорошо масштабируется, но он получит то, что вы ищете. Я думаю, что есть лучший способ сделать это, так что вы можете легко иметь N совпадающих элементов без большого влияния на код, но это в настоящее время ускользает от меня.
0

Я рекомендую следующую схему.

Objects: objectID, objectName
Tags: tagID, tagName
ObjectTag: objectID,tagID

Со следующим запросом.

select distinct
    objectName
from
    ObjectTab ot
    join object o
        on o.objectID = ot.objectID
    join tabs t
        on t.tagID = ot.tagID
where
    tagName in ('red','fruit')
0

Я бы предложил сделать вашу таблицу с 1 тегом на запись, например: 

 apple -> fruit
 apple -> red
 apple -> food
 banana -> fruit
 banana -> yellow
 banana -> food

Тогда вы могли бы просто

 SELECT object WHERE tag = 'fruit' OR tag = 'food';

Если вы действительно хотите сделать это по-своему, Вы можете сделать это так: 

 SELECT object WHERE tag LIKE 'red' OR tag LIKE '% red' OR tag LIKE 'red %' OR tag LIKE '% red %';
-2