Reverse relation support with foreign keys

[sumitsharansatsangi]

I didn't find any support for reverse relation support for related Table in the documentation.

Let's we have two table.

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


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

Now, While fetching Manager records, how can I fetch bands related to it too.

Currently, What I found as a solution is the following

bands = await Band.select(Band.name, Band.manager.all_columns())

It is using Band , not Manager , so the representation of the result changes.

Django-orm and SqlAlchemy supports this using related_name and back_ref respectively.

[sinisaos]

@sumitsharansatsangi Reverse FK lookups is currently not supported. There is discussion about what the reverse lookup API should look like, but that's about it. There is no implementation for it yet. Here is workaround if you need a list of bands related to one manager.

[sumitsharansatsangi]

Tried the following, received an error.

In [2]: managers = await Manager.select(Manager.name, Band.name).left_join(Band).on(Manager.id == Band.manager.id).run()
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Input In [2], in <cell line: 1>()
----> 1 managers = await Manager.select(Manager.name, Band.name).left_join(Band).on(Manager.id == Band.manager.id).run()

AttributeError: 'Select' object has no attribute 'left_join'

Have I done anything wrong??

[sumitsharansatsangi]

I don't think it was a solution. Probably, the discussion were about how it can be implemented.

I think for now, I should go with raw query.

[sinisaos]

@sumitsharansatsangi Not that example. You must use this example

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'], [])

or you can use raw query.

[sinisaos]

@sumitsharansatsangi There is another shorter way (so you don't have to write raw sql). Something like this

from piccolo.table import Table
from piccolo.columns import Integer, ForeignKey, Varchar, Boolean

class Manager(Table):
    name = Varchar()

    def get_bands(pk):
        return Band.select(Band.name).where(Band.manager.id == pk).run_sync()

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

manager = Manager.select().where(Manager.name == "Guido").first().run_sync()
bands = [i["name"] for i in Manager.get_bands(manager["id"])]

print({"manager": manager["name"], "bands": bands})