Joins

Joins are handled automatically by Piccolo. They work everywhere you’d expect (select queries, where clauses, etc.).

A fluent interface is used, which lets you traverse foreign keys.

Here’s an example of a select query which uses joins (using the example schema):

# This gets the band's name, and the manager's name by joining to the
# manager table:
>>> await Band.select(Band.name, Band.manager.name)

And a where clause which uses joins:

# This automatically joins with the manager table to perform the where
# clause. It only returns the columns from the band table though by default.
>>> await Band.select().where(Band.manager.name == 'Guido')

Left joins are used.

Improved static typing

You can optionally modify the above queries slightly for powerful static typing support from tools like Mypy and Pylance:

await Band.select(Band.name, Band.manager._.name)

Notice how we use ._. instead of . after each foreign key. An easy way to remember this is ._. looks a bit like a connector in a diagram.

Static type checkers now know that we’re referencing the name column on the Manager table, which has many advantages:

  • Autocompletion of column names.

  • Easier code navigation (command + click on column names to navigate to the column definition).

  • Most importantly, the detection of typos in column names.

This works, no matter how many joins are performed. For example:

await Concert.select(
    Concert.band_1._.name,
    Concert.band_1._.manager._.name,
)

Note

You may wonder why this syntax is required. We’re operating within the limits of Python’s typing support, which is still fairly young. In the future we will hopefully be able to offer identical static typing support for Band.manager.name and Band.manager._.name. But even then, the ._. syntax will still be supported.

join_on

Joins are usually performed using ForeignKey columns, though there may be situations where you want to join using a column which isn’t a ForeignKey.

You can do this using join_on.

It’s generally best to join on unique columns.