Are there any suggestions for one to many queries?

[sarvesh-deserve]

Are there any suggestions for one to many queries?

[dantownsend]

Something like this?

class Manager(Table):
    name = Varchar()

class Band(Table):
     manager = ForeignKey(Manager)
     name = Varchar()

So a manager has several bands.

Are you trying to query all bands for a manager? You can do something like:

from piccolo.query.methods.select import SelectRaw

await Manager.select(Manager.name, SelectRaw("ARRAY(SELECT name FROM band where manager = manager.id) AS band_names"))

[sinisaos]

I didn't get it at first, but it's basically a reverse lokoop. We have PR for that. @dantownsend solution is great for Postgres, but for SQlite (ARRAY is not supported) you can use a less efficient solution like this one (work both sync and async)

managers = Manager.select().run_sync()
bands = Band.select().run_sync()
# Maps manager_id to a list of bands
bands_map = {i[0]: list(i[1]) for i in it.groupby(bands, lambda x: x["manager"])}
rows = []
for manager in managers:
    rows.append(
        {
            "manager": manager["name"],
            "bands": [i["name"] for i in bands_map[manager["id"]]],
        }
    )
print(rows)