One to many relation

To illustrate how to implement one to many relations with the DAL, define another table “thing” that refers to the table “person” which we redefine here:

>>> db.define_table('person',
...                 Field('name'))
<Table person (id, name)>
>>> db.person.insert(name='Alex')
1
>>> db.person.insert(name='Bob')
2
>>> db.person.insert(name='Carl')
3
>>> db.define_table('thing',
...                 Field('name'),
...                 Field('owner_id', 'reference person'))
<Table thing (id, name, owner_id)>

Table “thing” has two fields, the name of the thing and the owner of the thing. The “owner_id” field is a reference field, it is intended that the field reference the other table by its id. A reference type can be specified in two equivalent ways, either:

Field('owner_id', 'reference person')

or:

Field('owner_id', db.person)

The latter is always converted to the former. They are equivalent except in the case of lazy tables, self references or other types of cyclic references where the former notation is the only allowed notation.

Now, insert three things, two owned by Alex and one by Bob:

>>> db.thing.insert(name='Boat', owner_id=1)
1
>>> db.thing.insert(name='Chair', owner_id=1)
2
>>> db.thing.insert(name='Shoes', owner_id=2)
3

You can select as you did for any other table:

>>> for row in db(db.thing.owner_id == 1).select():
...     print row.name
... 
Boat
Chair

Because a thing has a reference to a person, a person can have many things, so a record of table person now acquires a new attribute thing, which is a Set, that defines the things of that person. This allows looping over all persons and fetching their things easily:

>>> for person in db().select(db.person.ALL):
...     print person.name
...     for thing in person.thing.select():
...         print '    ', thing.name
...
Alex
     Boat
     Chair
Bob
     Shoes
Carl

Inner joins

Another way to achieve a similar result is by using a join, specifically an INNER JOIN. web2py performs joins automatically and transparently when the query links two or more tables as in the following example:

>>> rows = db(db.person.id == db.thing.owner_id).select()
>>> for row in rows:
...     print row.person.name, 'has', row.thing.name
... 
Alex has Boat
Alex has Chair
Bob has Shoes

Observe that web2py did a join, so the rows now contain two records, one from each table, linked together. Because the two records may have fields with conflicting names, you need to specify the table when extracting a field value from a row. This means that while before you could do:

row.name

and it was obvious whether this was the name of a person or a thing, in the result of a join you have to be more explicit and say:

row.person.name

or:

row.thing.name

There is an alternative syntax for INNER JOINS:

>>> rows = db(db.person).select(join=db.thing.on(db.person.id == db.thing.owner_id))
>>> for row in rows:
...     print row.person.name, 'has', row.thing.name
... 
Alex has Boat
Alex has Chair
Bob has Shoes

While the output is the same, the generated SQL in the two cases can be different. The latter syntax removes possible ambiguities when the same table is joined twice and aliased:

db.define_table('thing',
                Field('name'),
                Field('owner_id1', 'reference person'),
                Field('owner_id2', 'reference person'))
rows = db(db.person).select(
        join=[db.person.with_alias('owner_id1').on(db.person.id == db.thing.owner_id1),
              db.person.with_alias('owner_id2').on(db.person.id == db.thing.owner_id2)])

The value of join can be list of db.table.on(...) to join.

Left outer join

Notice that Carl did not appear in the list above because he has no things. If you intend to select on persons (whether they have things or not) and their things (if they have any), then you need to perform a LEFT OUTER JOIN. This is done using the argument “left” of the select. Here is an example:

>>> rows = db().select(db.person.ALL, db.thing.ALL,
...                    left=db.thing.on(db.person.id == db.thing.owner_id))
>>> for row in rows:
...     print row.person.name, 'has', row.thing.name
... 
Alex has Boat
Alex has Chair
Bob has Shoes
Carl has None

where:

left = db.thing.on(...)

does the left join query. Here the argument of db.thing.on is the condition required for the join (the same used above for the inner join). In the case of a left join, it is necessary to be explicit about which fields to select.

Multiple left joins can be combined by passing a list or tuple of db.mytable.on(...) to the left parameter.

Grouping and counting

When doing joins, sometimes you want to group rows according to certain criteria and count them. For example, count the number of things owned by every person. web2py allows this as well. First, you need a count operator. Second, you want to join the person table with the thing table by owner. Third, you want to select all rows (person + thing), group them by person, and count them while grouping:

>>> count = db.person.id.count()
>>> for row in db(db.person.id == db.thing.owner_id
...               ).select(db.person.name, count, groupby=db.person.name):
...     print row.person.name, row[count]
... 
Alex 2
Bob 1

Notice the count operator (which is built-in) is used as a field. The only issue here is in how to retrieve the information. Each row clearly contains a person and the count, but the count is not a field of a person nor is it a table. So where does it go? It goes into the storage object representing the record with a key equal to the query expression itself.

The count method of the Field object has an optional distinct argument. When set to True it specifies that only distinct values of the field in question are to be counted.