M2M¶

Note

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().