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.

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.