One to One

Schema

A one to one relationship is basically just a foreign key with a unique constraint. In Piccolo, you can do it like this:

from piccolo.table import Table
from piccolo.columns import ForeignKey, Varchar, Text

class Band(Table):
    name = Varchar()

class FanClub(Table):
    band = ForeignKey(Band, unique=True)  # <- Note the unique constraint
    address = Text()

Queries

Getting a related object

If we have a Band object:

band = await Band.objects().where(Band.name == "Pythonistas").first()

To get the associated FanClub object, you could do this:

fan_club = await FanClub.objects().where(FanClub.band == band).first()

Or alternatively, using get_related:

fan_club = await band.get_related(Band.id.join_on(FanClub.band))

Instead of using join_on, you can use reverse to traverse the foreign key backwards if you prefer:

fan_club = await band.get_related(FanClub.band.reverse())

Select

If doing a select query, and you want data from the related table:

>>> await Band.select(
...     Band.name,
...     Band.id.join_on(FanClub.band).address.as_alias("address")
... )
[{'name': 'Pythonistas', 'address': '1 Flying Circus, UK'}, ...]

Where

If you want to filter by related tables in the where clause:

>>> await Band.select(
...     Band.name,
... ).where(Band.id.join_on(FanClub.band).address.like("%Flying%"))
[{'name': 'Pythonistas'}]

Source

ForeignKey.reverse() → ForeignKey

If there’s a unique foreign key, this function reverses it.

class Band(Table):
    name = Varchar()

class FanClub(Table):
    band = ForeignKey(Band, unique=True)
    address = Text()

class Treasurer(Table):
    fan_club = ForeignKey(FanClub, unique=True)
    name = Varchar()

It’s helpful with get_related, for example:

>>> band = await Band.objects().first()
>>> await band.get_related(FanClub.band.reverse())
<Fan Club: 1>

It works multiple levels deep:

>>> await band.get_related(Treasurer.fan_club._.band.reverse())
<Treasurer: 1>