Задача MySQL для многих запросов

Вот моя проблема. У меня есть таблица "многие-ко-многим", называемая "user_has_personality". В моем приложении пользователи могут иметь множество личностей, а личность может принадлежать многим пользователям.

В таблице есть два целых столбца: user_id и person_id.

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

В качестве примера я хотел бы получить всех пользователей, которые имеют личности с идентификаторами 4, 5, 7, но также могут иметь некоторые другие личности. Но мне нужен запрос для работы с переменным количеством идентификаторов личности, например, 4, 5, 7, 9, 10.

Любые идеи?

Ответ 1

Этот запрос выполняет задание:

select  user_id
from    user_has_personalities
where   personality_id in (<list-of-personality-ids>)
group by user_id
having count(*) = <numer-of-items-in-IN-list>

Вам нужно предоставить список идентификаторов личности, разделенных запятыми, для <list-of-personality-ids>, и вам также необходимо указать количество элементов в списке. Придерживаясь вашего примера, вы получите:

select  user_id
from    user_has_personalities
where   personality_id in (4,5,7)
group by user_id
having count(*) = 3

это гарантирует, что вы получите только пользователей, у которых есть все эти личности.

Ответ 2

SELECT  *
FROM    (
        SELECT  DISTINCT user_id
        FROM    user_has_personalities
        ) uhpo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    user_has_personalities uhpi
        WHERE   uhpi.user_id  = uhpo.user_id
                AND personality_id IN (4, 5, 6, 9, 10)
        LIMIT 1 OFFSET 4
        )

Значение смещения должно быть 1 меньше, чем количество элементов в списке IN.

Если у вас есть список персонажей в отдельной таблице, используйте это:

SELECT  *
FROM    (
        SELECT  DISTINCT user_id
        FROM    user_has_personalities
        ) uhpo
WHERE   (
        SELECT  COUNT(*)
        FROM    perslist p
        JOIN    user_has_personalities uhpi
        ON      uhpi.user_id = uhpo.user_id
                AND uhpi.personality_id = p.id
        ) =
        (
        SELECT  COUNT(*)
        FROM    perslist
        )

Чтобы это работало корректно (и быстро), вам нужно иметь индекс UNIQUE на user_has_personalities (user_id, personality_id) (в этом порядке).

Если у вас есть таблица users, и почти все пользователи имеют запись в user_has_personalities, затем вместо нее вместо DISTINCT вложенного запроса:

SELECT  user_id
FROM    users uhpo
WHERE   (
        SELECT  COUNT(*)
        FROM    perslist p
        JOIN    user_has_personalities uhpi
        ON      uhpi.user_id = uhpo.user_id
                AND uhpi.personality_id = p.id
        ) =
        (
        SELECT  COUNT(*)
        FROM    perslist
        )

Ответ 3

SELECT a.user_id
FROM user_has_personalities a
JOIN user_has_personalities b ON a.user_id = b.user_id AND b.personality_id = 5
JOIN user_has_personalities c ON a.user_id = c.user_id AND b.personality_id = 7
WHERE a.personality_id = 4

Было бы легко создать этот список программно, но это не так просто, как поставка набора. С другой стороны, он эффективен.