How to avoid sub queries while joining tables?

Question

-- I am extra newbie here and feel this is either trivial or wrong database modelling --

In the below case:

create TABLE objects (
id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
name text unique
);

create TABLE features (
id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
name text
);

create TABLE features_map (
id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
o_id BIGINT REFERENCES objects ON DELETE restrict,
f_id BIGINT REFERENCES features ON DELETE restrict,
value text
);

insert into features(id, name) values 
(1, 'length'), 
(2, 'wheels'); 

insert into objects(id, name) values 
(1, 'car'), 
(2, 'bike'); 

insert into features_map(o_id,f_id,value) values
(1,1,'4.5m'),
(1,2,'4'),
(2,1,'2.3m'),
(2,2,'2');

I would like to get this desired output, i.e. left joining but merging results on a single row with different columns:

select o.id, o.name, 
(select value from features_map fm join features f on fm.f_id=f.id where fm.o_id=o.id and f.name='length') as length, 
(select value from features_map fm join features f on fm.f_id=f.id where fm.o_id=o.id and f.name='wheels') as wheels
from objects o;

id|name|length|wheels|
--|----|------|------|
 1|car |4.5m  |4     |
 2|bike|2.3m  |2     |

This type of query gets too slow with increasing size of tables e.g. objects count>10000 and features_map count>40000.

Using a join the query stays quite fast, but results appear (obviously) on multiple lines:

select *
from objects o
join features_map fm on o.id=fm.o_id
join features f on f.id=fm.f_id;

id|name|id|o_id|f_id|value|id|name  |
--|----|--|----|----|-----|--|------|
 1|car | 1|   1|   1|4.5m | 1|length|
 1|car | 2|   1|   2|4    | 2|wheels|
 2|bike| 3|   2|   1|2.3m | 1|length|
 2|bike| 4|   2|   2|2    | 2|wheels|

How can I get the desired output with the speed of the join approach?

Ciao, aaWnSd

Possible duplicate of [Create a pivot table with PostgreSQL](https://stackoverflow.com/questions/20618323/create-a-pivot-table-with-postgresql) — Juan Carlos Oropeza, Sep 02 '19 at 14:43
Hi, not sure this solves. Too complicated for me to understand and validate. Thank you anyhow for your help! — aAWnSD, Sep 02 '19 at 15:15
I know isn't that easy the first time you looks at it., but give it a try because that is the way to do it. — Juan Carlos Oropeza, Sep 02 '19 at 15:34

score 1 · Accepted Answer · answered Sep 02 '19 at 14:47

demo:db<>fiddle

You need a pivot table. This can be achieved by grouping your data set and then aggregate with filtered values.

In this case the MIN() function has been used, but it doesn't matter. You could use MAX() or SUM() as well because you only have just one value. So the MIN() of one single value == MAX() of this values == SUM() ...

SELECT
    o.id,
    o.name,
    MIN(value) FILTER (WHERE f.name = 'length') AS length,
    MIN(value) FILTER (WHERE f.name = 'wheels') AS wheels
FROM objects o
JOIN features_map fm ON o.id=fm.o_id
JOIN features f ON f.id=fm.f_id
GROUP BY o.id, o.name
ORDER BY o.id

How to avoid sub queries while joining tables?

1 Answers1