[Sqlalchemy-tickets] Issue #4355: Avoid unnecessary join on joinedload in custom query (zzzeek/sqlalchemy)

[Valery T. <iss...@bi...>]

New issue 4355: Avoid unnecessary join on joinedload in custom query
https://bitbucket.org/zzzeek/sqlalchemy/issues/4355/avoid-unnecessary-join-on-joinedload-in

Valery Tikhonov:

F.e. I have 2 entities: `Event` and `Plan`. Event has 1:1 relationship with plan via some foreign key.
And I build custom query to get all events with plans for some date and group.
Also I add `joinedload` to have all plans populated in relations.
```
query = Event.query \
        .join(Plan) \
        .filter(Plan.group_id == group_id) \
        .filter(Plan.date <= to_date) \
        .filter(Plan.color_id == color_id) \
        .options(joinedload(Event.plan, innerjoin=True))
```
This gives me such sql:
```
SELECT <event & plan selector>
FROM price_change 
JOIN plans_monthly ON plans_monthly.month_unique_id = price_change.month_unique_id JOIN plans_monthly AS plans_monthly_1 ON plans_monthly_1.month_unique_id = price_change.month_unique_id 
WHERE plans_monthly.group_id = ? AND plans_monthly.date <= ? AND plans_monthly.color_id = ? AND plans_monthly.date > ?
```

As you can see - it generates second plans_monthly join (just after the first one), which can be easily avoided, as all data, I need, it in the first one.

For now the workaround is building custom query (returning just plan and event rows), then mapping them to events via `from_statement` and then applying each found plan to event via `set_committed_value`.

But it would be nice to have it all done automatically.
Thanks!