Many to many

In the previous examples, we allowed a thing to have one owner but one person could have many things. What if Boat was owned by Alex and Curt? This requires a many-to-many relation, and it is realized via an intermediate table that links a person to a thing via an ownership relation.

Here is how to do it:

>>> db.define_table('person',
...                 Field('name'))
<Table person (id, name)>
>>> db.person.bulk_insert([dict(name='Alex'), dict(name='Bob'), dict(name='Carl')])
[1, 2, 3]
>>> db.define_table('thing',
...                 Field('name'))
<Table thing (id, name)>
>>> db.thing.bulk_insert([dict(name='Boat'), dict(name='Chair'), dict(name='Shoes')])
[1, 2, 3]
>>> db.define_table('ownership',
...                 Field('person', 'reference person'),
...                 Field('thing', 'reference thing'))
<Table ownership (id, person, thing)>

the existing ownership relationship can now be rewritten as:

>>> db.ownership.insert(person=1, thing=1)  # Alex owns Boat
1
>>> db.ownership.insert(person=1, thing=2)  # Alex owns Chair
2
>>> db.ownership.insert(person=2, thing=3)  # Bob owns Shoes
3

Now you can add the new relation that Curt co-owns Boat:

>>> db.ownership.insert(person=3, thing=1)  # Curt owns Boat too
4

Because you now have a three-way relation between tables, it may be convenient to define a new set on which to perform operations:

>>> persons_and_things = db((db.person.id == db.ownership.person) &
...                         (db.thing.id == db.ownership.thing))

Now it is easy to select all persons and their things from the new Set:

>>> for row in persons_and_things.select():
...     print row.person.name, 'has', row.thing.name
... 
Alex has Boat
Alex has Chair
Bob has Shoes
Curt has Boat

Similarly, you can search for all things owned by Alex:

>>> for row in persons_and_things(db.person.name == 'Alex').select():
...     print row.thing.name
... 
Boat
Chair

and all owners of Boat:

>>> for row in persons_and_things(db.thing.name == 'Boat').select():
...     print row.person.name
... 
Alex
Curt

A lighter alternative to many-to-many relations is tagging, you can found an example of this in the next section. Tagging is also discussed in the context of the IS_IN_DB and IS_IN_SET validators on chapter 7. Tagging works even on database backends that do not support JOINs like the Google App Engine NoSQL.