M2M¶
Note
There is a video tutorial on YouTube.
Sometimes in database design you need many-to-many (M2M) relationships.
For example, we might have our Band
table, and want to describe which genres of music
each band belongs to (e.g. rock and electronic). As each band can have multiple genres, a ForeignKey
on the Band
table won’t suffice. Our options are using an Array
/ JSON
/ JSONB
column, or using an M2M
relationship.
Postgres and SQLite don’t natively support M2M
relationships - we create
them using a joining table which has foreign keys to each of the related tables
(in our example, Genre
and Band
).

We create it in Piccolo like this:
from piccolo.columns.column_types import (
ForeignKey,
LazyTableReference,
Varchar
)
from piccolo.columns.m2m import M2M
from piccolo.table import Table
class Band(Table):
name = Varchar()
genres = M2M(LazyTableReference("GenreToBand", module_path=__name__))
class Genre(Table):
name = Varchar()
bands = M2M(LazyTableReference("GenreToBand", module_path=__name__))
# This is our joining table:
class GenreToBand(Table):
band = ForeignKey(Band)
genre = ForeignKey(Genre)
Note
We use LazyTableReference
because when Python evaluates Band
and
Genre
, the GenreToBand
class doesn’t exist yet.
By using M2M
it unlocks some powerful and convenient features.
Select queries¶
If we want to select each band, along with a list of genres that they belong to, we can do this:
>>> await Band.select(Band.name, Band.genres(Genre.name, as_list=True))
[
{"name": "Pythonistas", "genres": ["Rock", "Folk"]},
{"name": "Rustaceans", "genres": ["Folk"]},
{"name": "C-Sharps", "genres": ["Rock", "Classical"]},
]
You can request whichever column you like from the related table:
>>> await Band.select(Band.name, Band.genres(Genre.id, as_list=True))
[
{"name": "Pythonistas", "genres": [1, 2]},
{"name": "Rustaceans", "genres": [2]},
{"name": "C-Sharps", "genres": [1, 3]},
]
You can also request multiple columns from the related table:
>>> await Band.select(Band.name, Band.genres(Genre.id, Genre.name))
[
{
'name': 'Pythonistas',
'genres': [
{'id': 1, 'name': 'Rock'},
{'id': 2, 'name': 'Folk'}
]
},
...
]
If you omit the columns argument, then all of the columns are returned.
>>> await Band.select(Band.name, Band.genres())
[
{
'name': 'Pythonistas',
'genres': [
{'id': 1, 'name': 'Rock'},
{'id': 2, 'name': 'Folk'}
]
},
...
]
As we defined M2M
on the Genre
table too, we can get each band in a
given genre:
>>> await Genre.select(Genre.name, Genre.bands(Band.name, as_list=True))
[
{"name": "Rock", "bands": ["Pythonistas", "C-Sharps"]},
{"name": "Folk", "bands": ["Pythonistas", "Rustaceans"]},
{"name": "Classical", "bands": ["C-Sharps"]},
]
Objects queries¶
Piccolo makes it easy working with objects and M2M
relationship.
add_m2m¶
- Table.add_m2m(*rows: Table, m2m: M2M, extra_column_values: Dict[Union[Column, str], Any] = {}) M2MAddRelated
Save the row if it doesn’t already exist in the database, and insert an entry into the joining table.
>>> band = await Band.objects().get(Band.name == "Pythonistas") >>> await band.add_m2m( ... Genre(name="Punk rock"), ... m2m=Band.genres ... ) [{'id': 1}]
- Parameters:
extra_column_values –
If the joining table has additional columns besides the two required foreign keys, you can specify the values for those additional columns. For example, if this is our joining table:
class GenreToBand(Table): band = ForeignKey(Band) genre = ForeignKey(Genre) reason = Text()
We can provide the
reason
value:await band.add_m2m( Genre(name="Punk rock"), m2m=Band.genres, extra_column_values={ "reason": "Their second album was very punk." } )
get_m2m¶
- Table.get_m2m(m2m: M2M) M2MGetRelated
Get all matching rows via the join table.
>>> band = await Band.objects().get(Band.name == "Pythonistas") >>> await band.get_m2m(Band.genres) [<Genre: 1>, <Genre: 2>]
remove_m2m¶
- Table.remove_m2m(*rows: Table, m2m: M2M) M2MRemoveRelated
Remove the rows from the joining table.
>>> band = await Band.objects().get(Band.name == "Pythonistas") >>> genre = await Genre.objects().get(Genre.name == "Rock") >>> await band.remove_m2m( ... genre, ... m2m=Band.genres ... )
Hint
All of these methods can be run synchronously as well - for example,
band.get_m2m(Band.genres).run_sync()
.