Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
331 views
in Technique[技术] by (71.8m points)

sql - Using same column multiple times in WHERE clause

I have a following table structure.

USERS

USERS data

PROPERTY_VALUE

PROPERTY_VALUE data

PROPERTY_NAME

PROPERTY_NAME data

USER_PROPERTY_MAP

USER_PROPERTY_MAP data

I am trying to retrieve user/s from the users table who have matching properties in property_value table.

A single user can have multiple properties. The example data here has 2 properties for user '1', but there can be more than 2. I want to use all those user properties in the WHERE clause.

This query works if user has a single property but it fails for more than 1 properties:

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'

I understand since the query has pn.id = 1 AND pn.id = 2 it will always fail because pn.id can be either 1 or 2 but not both at the same time. So how can I re-write it to make it work for n number of properties?

In above example data there is only one user with id = 1 that has both matching properties used in the WHERE clause. The query should return a single record with all columns of the USERS table.

To clarify my requirements

I am working on an application that has a users list page on the UI listing all users in the system. This list has information like user id, user name, city etc. - all columns of the in USERS table. Users can have properties as detailed in the database model above.

The users list page also provides functionality to search users based on these properties. When searching for users with 2 properties, 'property1' and 'property2', the page should fetch and display only matching rows. Based on the test data above, only user '1' fits the bill.

A user with 4 properties including 'property1' and 'property2' qualifies. But a user with only one property 'property1' would be excluded due to the missing 'property2'.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

This is a case of . I added the tag.

Indexes

Assuming a PK or UNIQUE constraint on USER_PROPERTY_MAP(property_value_id, user_id) - columns in this order to make my queries fast. Related:

You should also have an index on PROPERTY_VALUE(value, property_name_id, id). Again, columns in this order. Add the the last column id only if you get index-only scans out of it.

For a given number of properties

There are many ways to solve it. This should be one of the simplest and fastest for exactly two properties:

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'

Not visiting table PROPERTY_NAME, since you seem to have resolved property names to IDs already, according to your example query. Else you could add a join to PROPERTY_NAME in each subquery.

We have assembled an arsenal of techniques under this related question:

For an unknown number of properties

@Mike and @Valera have very useful queries in their respective answers. To make this even more dynamic:

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);

Only add / remove rows from the VALUES expression. Or remove the WITH clause and the JOIN for no property filters at all.

The problem with this class of queries (counting all partial matches) is performance. My first query is less dynamic, but typically considerably faster. (Just test with EXPLAIN ANALYZE.) Especially for bigger tables and a growing number of properties.

Best of both worlds?

This solution with a recursive CTE should be a good compromise: fast and dynamic:

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 here

The manual about recursive CTEs.

The added complexity does not pay for small tables where the additional overhead outweighs any benefit or the difference is negligible to begin with. But it scales much better and is increasingly superior to "counting" techniques with growing tables and a growing number of property filters.

Counting techniques have to visit all rows in user_property_map for all given property filters, while this query (as well as the 1st query) can eliminate irrelevant users early.

Optimizing performance

With current table statistics (reasonable settings, autovacuum running), Postgres has knowledge about "most common values" in each column and will reorder joins in the 1st query to evaluate the most selective property filters first (or at least not the least selective ones). Up to a certain limit: join_collapse_limit. Related:

This "deus-ex-machina" intervention is not possible with the 3rd query (recursive CTE). To help performance (possibly a lot) you have to place more selective filters first yourself. But even with the worst-case ordering it will still outperform counting queries.

Related:

Much more gory details:

More explanation in the manual:


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...