Self-Reference and aliases

It is possible to define tables with fields that refer to themselves, here is an example:

db.define_table('person',
                Field('name'),
                Field('father_id', 'reference person'),
                Field('mother_id', 'reference person'))

Notice that the alternative notation of using a table object as field type will fail in this case, because it uses a table before it is defined:

db.define_table('person',
                Field('name'),
                Field('father_id', db.person),  # wrong!
                Field('mother_id', db['person']))  # wrong!

In general db.tablename and 'reference tablename' are equivalent field types, but the latter is the only one allowed for self-references.

When a table has a self-reference and you have to do join, for example to select a person and its father, you need an alias for the table. In SQL an alias is a temporary alternate name you can use to reference a table/column into a query (or other SQL statement).

With web2py you can make an alias for a table using the with_alias method. This works also for expressions, which means also for fields since Field is derived from Expression.

Here is an example:

>>> fid, mid = db.person.bulk_insert([dict(name='Massimo'), dict(name='Claudia')])
>>> db.person.insert(name='Marco', father_id=fid, mother_id=mid)
3
>>> Father = db.person.with_alias('father')
>>> Mother = db.person.with_alias('mother')
>>> type(Father)
<class 'pydal.objects.Table'>
>>> str(Father)
'person AS father'
>>> rows = db().select(db.person.name, Father.name, Mother.name,
...                    left=(Father.on(Father.id == db.person.father_id),
...                          Mother.on(Mother.id == db.person.mother_id)))
>>> for row in rows:
...     print row.person.name, row.father.name, row.mother.name
... 
Massimo None None
Claudia None None
Marco Massimo Claudia

Notice that we have chosen to make a distinction between:

• “father_id”: the field name used in the table “person”;
• “father”: the alias we want to use for the table referenced by the above field; this is communicated to the database;
• “Father”: the variable used by web2py to refer to that alias.

The difference is subtle, and there is nothing wrong in using the same name for the three of them:

>>> db.define_table('person',
...                 Field('name'),
...                 Field('father', 'reference person'),
...                 Field('mother', 'reference person'))
<Table person (id, name, father, mother)>
>>> fid, mid = db.person.bulk_insert([dict(name='Massimo'), dict(name='Claudia')])
>>> db.person.insert(name='Marco', father=fid, mother=mid)
3
>>> father = db.person.with_alias('father')
>>> mother = db.person.with_alias('mother')
>>> rows = db().select(db.person.name, father.name, mother.name,
...                    left=(father.on(father.id==db.person.father),
...                          mother.on(mother.id==db.person.mother)))
>>> for row in rows:
...     print row.person.name, row.father.name, row.mother.name
... 
Massimo None None
Claudia None None
Marco Massimo Claudia

But it is important to have the distinction clear in order to build correct queries.