Reverse foreign key lookups

[dantownsend]

Now we have M2M, it makes sense to add something similar for reverse foreign key lookups.

This needs doing before v1.

Here's an example API design:

class Manager(Table):
    name = Varchar()
    bands = ReverseLookup(LazyTableReference('Band', module_path=__module__))


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


>>> await Manager.select(Manager.name, Manager.bands(Band.name, as_list=True))
[
    {
        "name": "Guido",
        "bands": ["Pythonistas"]
    }
]

[AliSayyah]

@dantownsend Is it necessary to have a ReverseLookup field specified by the user?

class Band(Table):
    name = Varchar()
    manager = ForeignKey(Manager, reverse_lookup='bands')
   

[dantownsend]

@AliSayyah Good point - it could be done that way. The reverse lookup could then be created by the table metaclass. The only downside is it might not work as well with tab completion, but it's a simpler API for users.

[brnosouza]

And what about generic reverve lookups for that? That way we have the best of both worlds, the automatic reverse lookup and the tab completion for the ones that use that

[dantownsend]

One option is for the user to do something like this, which would give better tab completion:

class Manager(Table):
   name = Varchar()
   bands: ReverseLookup


class Band(Table):
    name = Varchar()
    manager = ForeignKey(Manager, reverse_lookup='bands')

[sinisaos]

@dantownsend We already have all the tools for reverse lookup without intervention in the source code. If we document properly, users will only have to write a few lines of code. My examples will be async FastAPI methods, but everything works as sync too. Here is a gist. What do you think about that?

[dantownsend]

@sinisaos I looked at the gist - what you've done is similar to how I currently do it.

I think having something built in would be nice. We'll get around to it eventually!

You could put a page here if you like: https://piccolo-orm.readthedocs.io/en/latest/piccolo/tutorials/index.html

[sinisaos]

@dantownsend Thank you for your reply. No need to add this to the docs because if anyone needs a workaround, they can find it here. I've updated the gist with your suggestion so it's all there.