PostgreSQL - Complicated Query Design

Question

I'm facing a design problematic, I have three tables:

var_storage:
id | var_name | var_value | user


post:
id | user

post_var_storage:
post_id | var_storage_id

Basically a post ManyToMany var_storage. var_storage contains var values, we can have datas like

var_storage(,MEAL,DINNER,)

var_storage(,FOOD,FRIES,)

etc ...

When a user creates a post we snapshot all its var_storage, that's why there is a many to many. ( And not post OneToMany var_storage, because other entities snapshots var_storage the same way ).

Then, our need is to query the database to find all the posts that have been created with some variables specific values.

Exemples:

"Find all rows from post created with MEAL=DINNER"
"Find all rows from post created with MEAL=DINNER AND FOOD=FRIES"
etc ...

The filtering is done on the user-side on multiple possible vars, so we cannot predict how much variables the user will want to filter on.

I setup a query pattern to see what it will look like and I want to know if there is an easier way to achieve it. Because in this one there is as much EXISTS clauses as filtered vars ( it possible to filter with 15 variables !! ), and the nested sub queries are really massive with multiple JOINs etc ..

Particularly because one of those is always the same ( the one is the FROM clause ).

SELECT * FROM 
post p
WHERE

    # MEAL=DINNER
    EXISTS (
        SELECT * FROM (
            SELECT * 
            FROM post p2 
            INNER JOIN post_var_storage pvs ON pvs.post_id = p2.id 
            INNER JOIN vars_storage vs ON pvs.var_storage_id = vs.id
            WHERE p2.id = p.id
        ) vs 
        WHERE 
            vs.var_name = "MEAL" AND vs.value = "DINNER"
    ) AND

    # FOOD=FRIES
    EXISTS (
        SELECT * FROM (
            SELECT * 
            FROM post p2 
            INNER JOIN post_var_storage pvs ON pvs.post_id = p2.id 
            INNER JOIN vars_storage vs ON pvs.var_storage_id = vs.id
            WHERE p2.id = p.id
        ) vs 
        WHERE 
            vs.var_name = "FOOD" AND vs.value = "FRIES"
    ) AND

    ....

I've had similar, but simpler situations; what I've done is dynamically create a prepared statement, first build the query - it's basically repetitive with some simple substitution, and then populate the values. Build two functions, one to create the query string from the input set, and another to populate the values (just track the index). — user1676075
– user1676075, Commented Jul 16, 2018 at 16:46
Thank you, the thing is that I already use Doctrine ORM, my problem is really about performances not building the query .. — NathanVss
– NathanVss, Commented Jul 17, 2018 at 8:14

Carsten Massmann · Accepted Answer · 2018-07-16 17:28:36Z

1

You could use a CTE (common table expression) to reduce the sql code, like here:

WITH cte AS (
  SELECT p.id pid, var_name vn, var_value vv 
  FROM post p
  INNER JOIN post_var_storage pvs ON pvs.post_id = p.id 
  INNER JOIN vars_storage vs ON pvs.var_storage_id = vs.id
)
select * from  post 
 where exists (select 1 from cte where id=pid and vn='Meal' and vv='Dinner')
   and exists (select 1 from cte where id=pid and vn='Food' and vv='Fries')
// and exists (select 1 from cte where id=pid and vn= ...   and vv= ...   )

Check out the little fiddle I prepared here: http://rextester.com/TPGJ34272

With the test data

vars_storage:
--------------
id  var_name    var_value
1   Food        Fries
2   Meal        Dinner
3   Breakfast   Cereals
4   Supper      Soup

post:
------
id  user
1   Charles
2   Fiona
3   Patty
4   Joe
5   Rita
6   Harry
7   Meghan
8   Anne

post_var_storage:
------------------
post_id var_storage_id
1       1
1       2
2       4
3       1
3       2
4       2
4       3
5       4
5       1
6       1
6       2

The above query returns:

id  user
6   Harry
1   Charles
3   Patty

answered Jul 16, 2018 at 17:28

Carsten Massmann

28.4k3 gold badges25 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NathanVss Over a year ago

Thank you, please check the reply I added, without EXISTS nor sub queries.

NathanVss · Accepted Answer · 2018-07-17 16:56:19Z

0

I created I think on really more efficient query without EXISTS nor sub queries, your point ?

http://rextester.com/OWTG94040

answered Jul 17, 2018 at 16:56

NathanVss

6447 silver badges18 bronze badges

2 Comments

Carsten Massmann Over a year ago

No idea, whether it is more efficient, but it certainly works! And that is the main thing.

Carsten Massmann Over a year ago

You should make sure though, that the columns var_name and var_value are defined as unique, since otherwise double entries could mess up your result.

Collectives™ on Stack Overflow

PostgreSQL - Complicated Query Design

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related