I have the following tables in my postgres database
- power_relations - (id, properties)
- power_relation_members - (id, power_relation_id, member_id, member_type)
- power_lines - (id, geom)
- power_nodes - (id, geom)
A power_relation
can have multiple members which can either be power_nodes
or power_lines
. I use power_relation_members
to track this polymorphic many-to-many relation.
The query I need to write has to return all the power_lines
that belong to power_relations
that contains power_lines
matching a condition.
For example, if I have a
powerlines:
id, geom
----------
1, abc
2, d
3, g
4, j
5, m
6, abc
power_relation_members:
member_id, power_relation_id
----------------------------
1, r_1
2, r_1
3, r_1
4, r_2
5, r_2
power_relations:
id, properties
--------------
r_1, "relation1"
r_2, "relation2"
In the above data, line 1 and 6 have the geom = 'abc'. Lines 1 - 3 are related to relation 1, lines 4-5 to relation 2 and no relation for line 6.
I would want to get all the power_lines
that are related (via power_relation
to the power_line
whose geom is abc
. That means, I need lines 1, 2, 3 as results from my query.
I could achieve my result in two steps:
- Get the distinct id's of
power_relations
that havepower_lines
that match my condition - Get all the
power_lines
that are related to thepower_relations
whose id is in the ids fetched from step 1.
Instead, I wanted to do this all in one query by doing the step 1 in a subquery. This includes joining the same tables in the subquery and its outer query. This is how my query looks and it gives me the results as I expected:
SELECT DISTINCT l.id FROM powerline l
JOIN power_relation_members m
ON (l.id = m.member_id AND m.member_type = 'way')
JOIN power_relations r
ON (m.power_relation_id = r.id
AND r.id in (
SELECT DISTINCT s_r.id FROM powerline s_l
JOIN power_relation_members s_m
ON (s_l.id = s_m.member_id AND s_m.member_type = 'way')
JOIN power_relations s_r
on (s_m.power_relation_id = s_r.id)
WHERE s_l.geom = 'somestring'
)
);
You may see the simulation in the fiddle here http://sqlfiddle.com/#!15/2493b
I'm not very familiar with how the database's perform and I would like to know if this single query is optimal compared to the two step individual queries or if there is a better and optimal way to achieve the same.
Thank you.