Использование одного и того же столбца несколько раз в предложении WHERE

Question

У меня есть следующая структура таблицы.

USERS

PROPERTY_VALUE

PROPERTY_NAME

USER_PROPERTY_MAP

Я пытаюсь получить пользователя / ей из users таблица с подходящими свойствами в property_value Таблица.

Один пользователь может иметь несколько свойств. Пример данных здесь имеет 2 свойства для пользователя '1', но их может быть больше 2. Я хочу использовать все эти пользовательские свойства в WHERE пункт.

Этот запрос работает, если у пользователя есть одно свойство, но он не работает для более чем 1 свойства:

SELECT * FROM users u
INNER JOIN user_property_map upm ON u.id = upm.user_id
INNER JOIN property_value pv ON upm.property_value_id = pv.id
INNER JOIN property_name pn ON pv.property_name_id = pn.id
WHERE (pn.id = 1 AND pv.id IN (SELECT id FROM property_value WHERE value like '101')
AND pn.id = 2 AND pv.id IN (SELECT id FROM property_value WHERE value like '102')) and u.user_name = 'user1' and u.city = 'city1'

Я понимаю, так как запрос имеет pn.id = 1 AND pn.id = 2 это всегда потерпит неудачу, потому что pn.id может быть 1 или 2, но не оба одновременно. Так, как я могу переписать это, чтобы заставить это работать для n числа свойств?

В приведенном выше примере данных есть только один пользователь с id = 1 который имеет оба соответствующих свойства, используемые в WHERE пункт. Запрос должен возвращать одну запись со всеми столбцами USERS Таблица.

Чтобы уточнить мои требования

Я работаю над приложением, в котором на пользовательском интерфейсе есть страница со списком пользователей, в которой перечислены все пользователи системы. Этот список содержит информацию, такую ​​как идентификатор пользователя, имя пользователя, город и т. Д. - все столбцы в USERS Таблица. Пользователи могут иметь свойства, подробно описанные в модели базы данных выше.

Страница списка пользователей также предоставляет функции для поиска пользователей на основе этих свойств. При поиске пользователей с 2 ​​свойствами, "property1" и "property2", страница должна извлекаться и отображать только совпадающие строки. На основании приведенных выше тестовых данных, только пользователь "1" отвечает требованиям.

Квалифицируется пользователь с 4 свойствами, включая "property1" и "property2". Но пользователь, имеющий только одно свойство 'property1', будет исключен из-за отсутствия свойства 'property2'.

Answer 1

Это случай реляционного деления. Я добавил тег к вопросу.

Индексы

Предполагая PK или УНИКАЛЬНОЕ ограничение на USER_PROPERTY_MAP(property_value_id, user_id) - столбцы в этом порядке, чтобы сделать мои запросы быстрыми. Связанные с:

• Составной индекс также хорош для запросов по первому полю?

Вы также должны иметь индекс на PROPERTY_VALUE(value, property_name_id, id), Опять столбцы в таком порядке. Добавьте последний столбец id только если вы получаете только индексные сканы.

Для данного количества свойств

Есть много способов ее решить. Это должно быть одним из самых простых и быстрых точно для двух свойств:

SELECT u.*
FROM   users             u
JOIN   user_property_map up1 ON up1.user_id = u.id
JOIN   user_property_map up2 USING (user_id)
WHERE  up1.property_value_id =
      (SELECT id FROM property_value WHERE property_name_id = 1 AND value = '101')
AND    up2.property_value_id =
      (SELECT id FROM property_value WHERE property_name_id = 2 AND value = '102')
-- AND    u.user_name = 'user1'  -- more filters?
-- AND    u.city = 'city1'

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

Мы собрали арсенал методов по этому смежному вопросу:

• Как отфильтровать результаты SQL в сквозном отношении

Для неизвестного количества свойств

У @Mike и @Valera есть очень полезные запросы в соответствующих ответах. Чтобы сделать это еще более динамичным:

WITH input(property_name_id, value) AS (
      VALUES  -- provide n rows with input parameters here
        (1, '101')
      , (2, '102')
      -- more?
      ) 
SELECT *
FROM   users u
JOIN  (
   SELECT up.user_id AS id
   FROM   input
   JOIN   property_value    pv USING (property_name_id, value)
   JOIN   user_property_map up ON up.property_value_id = pv.id
   GROUP  BY 1
   HAVING count(*) = (SELECT count(*) FROM input)
   ) sub USING (id);

Добавлять / удалять только строки из VALUES выражение. Или удалите WITH пункт и JOIN без фильтров свойств вообще.

Проблема с этим классом запросов (считая все частичные совпадения) заключается в производительности. Мой первый запрос менее динамичен, но обычно значительно быстрее. (Просто проверить с EXPLAIN ANALYZE.) Особенно для больших столов и растущего числа свойств.

Лучшее из обоих миров?

Это решение с рекурсивным CTE должно быть хорошим компромиссом: быстрым и динамичным:

WITH RECURSIVE input AS (
   SELECT count(*)     OVER () AS ct
        , row_number() OVER () AS rn
        , *
   FROM  (
      VALUES  -- provide n rows with input parameters here
        (1, '101')
      , (2, '102')
      -- more?
      ) i (property_name_id, value)
   )
 , rcte AS (
   SELECT i.ct, i.rn, up.user_id AS id
   FROM   input             i
   JOIN   property_value    pv USING (property_name_id, value)
   JOIN   user_property_map up ON up.property_value_id = pv.id
   WHERE  i.rn = 1

   UNION ALL
   SELECT i.ct, i.rn, up.user_id
   FROM   rcte              r
   JOIN   input             i ON i.rn = r.rn + 1
   JOIN   property_value    pv USING (property_name_id, value)
   JOIN   user_property_map up ON up.property_value_id = pv.id
                              AND up.user_id = r.id
   )
SELECT u.*
FROM   rcte  r
JOIN   users u USING (id)
WHERE  r.ct = r.rn;          -- has all matches

dbfiddle здесь

Руководство по рекурсивным CTE.

Дополнительная сложность не окупается для небольших столов, где дополнительные накладные расходы перевешивают любую выгоду или разница незначительна для начала. Но он масштабируется гораздо лучше и все больше превосходит методы "подсчета" с растущими таблицами и растущим числом фильтров свойств.

Методы подсчета должны посещать все ряды в user_property_map для всех заданных фильтров свойств, в то время как этот запрос (а также 1-й запрос) может устранить ненужных пользователей на раннем этапе.

Оптимизация производительности

С текущей статистикой таблицы (разумные настройки, autovacuum работает), Postgres обладает знаниями о "наиболее распространенных значениях" в каждом столбце и будет переупорядочивать объединения в 1-м запросе, чтобы сначала оценить наиболее селективные фильтры свойств (или, по крайней мере, не наименее селективные). До определенного предела: join_collapse_limit, Связанные с:

• Postgresql join_collapse_limit и время для планирования запросов
• Почему небольшое изменение в поисковом запросе так сильно замедляет запрос?

Это вмешательство "deus-ex-machina" невозможно с 3-м запросом (рекурсивный CTE). Чтобы повысить производительность (возможно, многое), вы должны сначала установить более селективные фильтры. Но даже при наихудшем порядке это все равно превзойдет количество запросов.

Связанные с:

• Проверка статистики целей в PostgreSQL

Гораздо больше кровавых подробностей:

• Частичный индекс PostgreSQL не используется при создании таблицы с существующими данными

Больше объяснений в руководстве:

• Статистика, используемая планировщиком

Answer 2

SELECT *
  FROM users u
 WHERE u.id IN(
         select m.user_id
           from property_value v
           join USER_PROPERTY_MAP m
             on v.id=m.property_value_id 
          where (v.property_name_id, v.value) in( (1, '101'), (2, '102') )
          group by m.user_id
         having count(*)=2
      )

ИЛИ ЖЕ

SELECT u.id
  FROM users u
 INNER JOIN user_property_map upm ON u.id = upm.user_id
 INNER JOIN property_value pv ON upm.property_value_id = pv.id
 WHERE (pv.property_name_id=1 and pv.value='101')
    OR (pv.property_name_id=2 and pv.value='102')
 GROUP BY u.id
HAVING count(*)=2

нет property_name таблица, необходимая в запросе, если имя_свойства установлено.

Answer 3

Если вы хотите просто отфильтровать:

SELECT users.*
FROM users
where (
    select count(*)
    from user_property_map
    left join property_value on user_property_map.property_value_id = property_value.id
    left join property_name on property_value.property_name_id = property_name.id
    where user_property_map.user_id = users.id -- join with users table
    and (property_name.name, property_value.value) in (
        values ('property1', '101'), ('property2', '102') -- filter properties by name and value
    )
) = 2 -- number of properties you filter by

Или, если вам нужны пользователи, упорядоченные по убыванию числа совпадений, вы можете сделать:

select * from (
    SELECT users.*, (
        select count(*) as property_matches
        from user_property_map
        left join property_value on user_property_map.property_value_id = property_value.id
        left join property_name on property_value.property_name_id = property_name.id
        where user_property_map.user_id = users.id -- join with users table
        and (property_name.name, property_value.value) in (
            values ('property1', '101'), ('property2', '102') -- filter properties by name and value
        )
    )
    FROM users
) t
order by property_matches desc

Answer 4

SELECT * FROM users u
INNER JOIN user_property_map upm ON u.id = upm.user_id
INNER JOIN property_value pv ON upm.property_value_id = pv.id
INNER JOIN property_name pn ON pv.property_name_id = pn.id
WHERE (pn.id = 1 AND pv.id IN (SELECT id FROM property_value WHERE value 
like '101') )
OR ( pn.id = 2 AND pv.id IN (SELECT id FROM property_value WHERE value like 
'102'))

OR (...)
OR (...)

Вы не можете сделать AND, потому что нет такого случая, когда id равен 1 и 2 для ОДНОГО ЖЕ, а вы задаете условие where для каждой строки!

Если вы запустите простой тест, как

SELECT * FROM users where id=1 and id=2 

Вы получите 0 результатов. Для достижения этого использования

 id in (1,2) 

или же

 id=1 or id=2

Этот запрос можно оптимизировать больше, но я надеюсь, что это хорошее начало.

Answer 5

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

SELECT DISTINCT u.* 
  FROM Users u INNER JOIN USER_PROPERTY_MAP upm ON u.id = upm.[user_id]
                INNER JOIN PROPERTY_VALUE pv ON upm.property_value_id = pv.id
                INNER JOIN PROPERTY_NAME pn ON pv.property_name_id = pn.id

  WHERE (pn.id = 1 AND pv.[value] = '101')
     OR (pn.id = 2 AND pv.[value] = '102')

Обратите внимание, что я использовал pv.[value] = вместо подзапроса для получения идентификатора... это упрощение.

Answer 6

Ты используешь AND оператор между двумя pn.id=1 а также pn.id=2, тогда как вы получите ответ между этим:

(SELECT id FROM property_value WHERE value like '101') and
(SELECT id FROM property_value WHERE value like '102') 

Так как выше комментарии, используйте or оператор.

Обновление 1:

SELECT * FROM users u
INNER JOIN user_property_map upm ON u.id = upm.user_id
INNER JOIN property_value pv ON upm.property_value_id = pv.id
INNER JOIN property_name pn ON pv.property_name_id = pn.id
WHERE pn.id in (1,2) AND pv.id IN (SELECT id FROM property_value WHERE value like '101' or value like '102');

Answer 7

Если я правильно понимаю ваш вопрос, я бы сделал это так.

SELECT u.id, u.user_name, u.city FROM users u 
WHERE (SELECT count(*) FROM property_value v, user_property_map m 
WHERE m.user_id = u.id AND m.property_value_id = v.id AND v.value IN ('101', '102')) = 2

Это должно вернуть список пользователей, которые имеют все свойства, перечисленные в предложении IN. 2 представляет количество искомых свойств.

Answer 8

Предполагая, что вы хотите выбрать все поля в таблице USERS

SELECT u.* 
FROM USERS u
INNER JOIN 
(
    SELECT USERS.id as user_id, COUNT(*) as matching_property_count
    FROM USERS
    INNER JOIN (
        SELECT m.user_id, n.name as property_name, v.value
        FROM PROPERTY_NAME n
        INNER JOIN PROPERTY_VALUE v ON n.id = v.property_name_id
        INNER JOIN USER_PROPERTY_MAP m ON m.property_value_id = v.property_value_id
        WHERE  (n.id = @property_id_1 AND v.value = @property_value_1) -- Property Condition 1
            OR (n.id = @property_id_2 AND v.value = @property_value_2) -- Property Condition 2
            OR (n.id = @property_id_3 AND v.value = @property_value_3) -- Property Condition 3
            OR (n.id = @property_id_N AND v.value = @property_value_N) -- Property Condition N
    ) USER_PROPERTIES ON USER_PROPERTIES.user_id = USERS.id
    GROUP BY USERS.id
    HAVING COUNT(*) = N     --N = the number of Property Condition in the WHERE clause
    -- Note : 
    -- Use HAVING COUNT(*) = N if property matches will be "MUST MATCH ALL"
    -- Use HAVING COUNT(*) > 0 if property matches will be "MUST MATCH AT LEAST ONE"
) USER_MATCHING_PROPERTY_COUNT ON u.id = USER_MATCHING_PROPERTY_COUNT.user_id