Left join - Get all Managers with their Bands if they exist

[davidolrik]

Is it possible to get all Manager objects with their Bands in one go? - Even if some managers don't have Bands.

[dantownsend]

There's isn't an API for this currently.

The best workaround I can think of is to do two separate queries, and then splice them together using Python:

managers = Manager.select().run_sync()
bands = Band.select(Band.name, Band.manager.id).order_by(Band.manager.id).run_sync()

band_map = {}

for key, group in itertools.groupby(
    bands,
    lambda x: x['manager.id']
):
    band_map[key] = list(group)

for manager in managers:
    manager['bands'] = band_map.get(manager['id'], [])

I can convert this discussion into an issue, so a proper API can be implemented. I'm not sure what the perfect API would be at the moment. Something like this?

>>> await Manager.select(Manager.name, Manager.band_set(Band.name)).run()
[{'name': 'Guido', 'bands': [{'name': 'Pythonistas'}]}]

[davidolrik]

I was thinking something more like this:

managers = await Manager.objects().where(prefetch=[Band]).run()

This would result in a select that left joins managers and bands, as to get what's in the where clause plus related stuff.
We could even allow the prefetch to be nested, like so:

managers = await Manager.objects().where(prefetch=[Band.location]).run()

Where location is a relation as well, resulting in a left join first with bands, and then with the bands locations.

[maxandix]

Let's add left_join and inner_join statements like this:

managers = await Manager.select(Manager.name, Band.name).left_join(Band).on(Manager.id == Band.manager.id).run()

It's clean, powerful and consistent with the framework idea. It's obvious syntax which doesn't have any magic.