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)
power_relation can have multiple members which can either be
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_linesthat match my condition
- Get all the
power_linesthat are related to the
power_relationswhose 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.