One-to-many relations

[McSlow]

Hi,
i'm currently having some beginner issues with one-to-many relations. Given the following scheme:

class Manager(Table):
    id = PrimaryKey()
    name = Varchar()

class Band(Table):
    id = PrimaryKey()
    name = Varchar()
    manager = ForeignKey(references=Manager)
    popularity = Integer()

How can I access all Bands a Manager manages?
So something like bandsAManagerManages = Manager.bands
Is there some automatic support for such things or do I need to write a function with a subquery in the Manager table (... .where(manager=self.id)?

[dantownsend]

Hi @McSlow

If you just just want to find the bands for a single manager, you can do something like:

manager = Manager.objects().get(Manager.name == 'Guido').run_sync()

>>> Band.objects().where(Band.manager == manager).run_sync()
[<Band: 1>]

There isn't an API yet to bulk retrieve all of the bands for all of the managers (i.e. reverse foreign key lookups), but it's something we're working on.

If that's your use case, you might find this discussion useful: #208

[McSlow]

Thanks- that was fast :)
Reverse foreign key lookups would be cool, but the provided example already does exactly what I want :)
Just need to find out how to add this as an rest endpoint to fastAPi ( /manager/1/bands) but I guess I'll find out...

[dantownsend]

The best way to see how it all hangs together with FastAPI, is use piccolo asgi new (assuming it's a new project). That includes some example endpoints.

[McSlow]

Ah ok- that was really easy, got it. Thanks again :)

[metakot]

Any news on this?
There is already working m2m functionality for selecting related tables await Band.select(Band.name, Band.genres(Genre.id, Genre.name)), so it can be applied for reverse one-to-many lookups.

[dantownsend]

@metakot We have a prototype for this in a PR - #599. Hopefully will get it merged soon.