SQL-запрос для фильтрации по группе связанных строк

У меня есть постоянная проблема в SQL-запросах, которую я не смог элегантно решить ни в сыром SQL, ни в Django ORM, а теперь я столкнулся с ней и в EntityFramework. Возможно, она достаточно распространена, чтобы иметь собственное название, но я его не знаю.

Допустим, у меня есть простая связь внешнего ключа между двумя таблицами, например, 

Book 1 <- * Tag

У книги много тегов, а у тега одна книга, т.е. таблица Tag имеет внешний ключ к таблице book.

Теперь я хочу найти все книги, которые имеют "Tag1" и "Tag2".

Raw SQL

Я могу сделать несколько соединений

SELECT * FROM books
JOIN tags t1 on tags.book_id = books.id
JOIN tags t2 on tags.book_id = books.id
WHERE t1.tag = 'Tag1' AND t2.tag = 'Tag2'

Круто, это работает, но не очень похоже на производительность

Django

В django я мог бы сделать нечто подобное

Book.objects.filter(tags__tag="Tag1").filter(tags__tag="Tag1")

Изменение фильтров таким образом вызовет дополнительные соединения, как в необработанной версии SQL

EntityFramework LINQ

Я попробовал составить цепочку .Where() аналогично изменению .filter() в Django, но это не дает того же результата. Он построит запрос, похожий на следующий, который, конечно, ничего не вернет, потому что нет строки, где тегом являются две разные строки

SELECT * FROM books
JOIN tags t1 on tags.book_id = books.id
WHERE t1.tag = 'Tag1' AND t1.tag = 'Tag2'

Завершение

Полагаю, я мог бы сделать агрегацию массива для объединения тегов в массив и сравнения с ним, но это тоже кажется дорогим, а агрегация и группировка также влияют на порядок вещей, что заставляет меня делать подзапросы для получения нужного мне порядка.

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

  1. Способ имитировать глупую ерунду, описанную выше, в LINQ
  2. Альтернативный, более элегантный подход, который позволит мне сделать то, что мне нужно, и который хорошо работает с любым ORM
    3. .

Дополнительные бредни

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

С group by и having мы можем сделать Tag 1 И Tag 2:

with cte_tags as (
select book_id
  from tags
 where tag in ('Tag 1', 'Tag 2')
 group by book_id
 having count(*)=2)
select b.id as book_id,
       b.name
  from books b
  join cte_tags t
    on b.id = t.book_id;

Если я правильно понимаю, вам нужны книги, которые только имеют теги 'Tag1' и 'Tag2'. Т.е. никаких других тегов. Я не знаю официального названия для этой проблемы, возможно, exclusive contains.

Это означает поиск книг, удовлетворяющих двум условиям:

  • иметь все теги в ("Tag1", "Tag2")
  • иметь два уникальных тега

Поскольку вы ищете решение на основе Entity-Framework, вот способ сделать это на LINQ:

var tags = new[] { "Tag1", "Tag2" };
var books = context.Books
    .Where(b => b.Tags.All(t => tags.Contains(t.Tag)) 
        && b.Tags.Select(t.Tag).Distinct().Count == tags.Count());

Второе условие необходимо, иначе книги без тегов также были бы выбраны (такова семантика All).

Если производительность важна, вы должны попробовать различные запросы на вашем сервере с реальными данными и измерить их производительность.

У меня есть общее замечание.

Запрос, подобный этому:

select book_id
from tags
where tag in ('Tag1', 'Tag2')

или вот так: 

select book_id
from tags
where tag = 'Tag 1' OR tag = 'Tag2'

обычно приводит к сканированию всей таблицы tags даже если она имеет индекс на столбце tag.

С другой стороны, запрос, подобный этому:

select book_id
from tags
where tag = 'Tag1'

обычно используется индекс.

Итак, мы можем расширить запрос с OR на два отдельных запроса и затем объединить их результаты.

WITH
CTE_BookIDs
AS
(
    select book_id
    from tags
    where tag = 'Tag1'

    INTERSECT

    select book_id
    from tags
    where tag = 'Tag2'
)
SELECT
    books.*
FROM
    books
    INNER JOIN CTE_BookIDs ON CTE_BookIDs.book_id = books.id
;

Вот запрос по выборочному набору данных:

CREATE TABLE #Tags
    (ID int IDENTITY NOT NULL PRIMARY KEY
    ,BookID int NOT NULL
    ,Tag varchar(50) NOT NULL);

INSERT INTO #Tags VALUES
(1, 'Tag1'),
(1, 'Tag2'),
(1, 'Tag3'),
(1, 'Tag4'),
(2, 'Tag1'),
(3, 'Tag2'),
(4, 'Tag1'),
(4, 'Tag2'),
(4, 'Tag3'),
(5, 'Tag3'),
(5, 'Tag4'),
(5, 'Tag5'),
(6, 'Tag1'),
(6, 'Tag3'),
(6, 'Tag5'),
(7, 'Tag2'),
(7, 'Tag3'),
(8, 'Tag1'),
(8, 'Tag2');

CREATE INDEX IX_Tag ON #Tags
(
    Tag, BookID
);

WITH
CTE_BookIDs
AS
(
    select BookID
    from #Tags
    where tag = 'Tag1'

    INTERSECT

    select BookID
    from #Tags
    where tag = 'Tag2'
)
SELECT *
FROM CTE_BookIDs
;

DROP TABLE #Tags;

Результат

+--------+
| BookID |
+--------+
|      1 |
|      4 |
|      8 |
+--------+

План выполнения

execution plan

Попробуйте следующее решение:

Сначала создайте индекс для ускорения этого запроса

create index on tags (tag, book_id);

Секунда, проверка следующего запроса

SELECT * FROM books
JOIN tags t1 on t1.tag = 'Tag1' AND t2.book_id = books.id
JOIN tags t2 on t2.tag = 'Tag2' AND t2.book_id = books.id;

Если вы хотите работать с произвольным списком из любого количества тегов:

SELECT 
   books.id,
   count(distinct tags.id) as tags_count
FROM books
JOIN tags on tags.tag = ANY(['Tag1', 'Tag2', ...]) AND tags.book_id = books.id
GROUP BY books.id
HAVING 
   count(distinct tags.id) = <number of tags>

Для ускорения второго запроса проверьте, поможет ли следующий индекс

create index on tags (book_id, tag);
Вернуться на верх