CODEWITHSUNDEEP
postgresql
aAWnSD
aAWnSD

Reputation: 134

How to avoid sub queries while joining tables?

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

Upvotes: 1

Views: 35

Answers (1)

S-Man
S-Man

Reputation: 23726

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

Upvotes: 1

Related Questions