eagerload creates a redundant join when the loaded relation is already joined

(original reporter: ants) Given an Parent has many Child type of schema, there should be a way to query Child, eagerload its parent and filter by the parents attributes without joining the parent table twice. By doing

session.query(Child).options(eagerload('parent')).join('parent').filter_by(name='xyz')

we get SQL like:

SELECT child.id AS child_id, child.parent_id AS child_parent_id, child.name AS child_name, parent_1.id AS parent_1_id, parent_1.name AS parent_1_name
FROM child JOIN parent ON parent.id = child.parent_id LEFT OUTER JOIN parent AS parent_1 ON parent_1.id = child.parent_id
WHERE parent.name = ? ORDER BY child.oid, parent_1.oid

It would be preferrable to have this:

SELECT child.id AS child_id, child.parent_id AS child_parent_id, child.name AS child_name, parent.id AS parent_id, parent.name AS parent_name
FROM child JOIN parent ON parent.id = child.parent_id 
WHERE parent.name = ? ORDER BY child.oid, parent.oid

That is, reuse the regular joins that follow exclusively to-one relation paths for eagerloads.

Comments (3)