1.5.4 Many-To-Many Mappings

In a real life more than one person might live at the same address and each person might have multiple addresses. The relationship is actually a many-to-many mapping. Have a look at the code below:

import web.database, web.database.object
connection = web.database.connect(type="sqlite", database="object-multiple.db")
cursor = connection.cursor() 

person = web.database.object.Table("Person")
person.addColumn(web.database.object.String(name="firstName"))
person.addColumn(web.database.object.String(name="surname"))
person.addRelated(name="addresses", foreignTable="Address")

address = web.database.object.Table("Address")
address.addColumn(web.database.object.String(name="firstLine"))
address.addColumn(web.database.object.String(name="postcode"))
address.addRelated(name="person", foreignTable="Person")

database = web.database.object.Database()
database.addTable(person)
database.addTable(address)
database.init(cursor)

We have now related the two tables using the addRelated() method of each class instead of using addMultiple() and addSingle().

Note: Because the two Classes use related joins the database.createTables() method actually creates an intermediate table to store the relationships. The modules hide this table so you don't need to worry about it to use web.database.object. If you are interested the table is named by taking the two tables in alphabetical order and joining thier names with an underscore. For example the table in the example above will create a table names 'Adrress_Person'. This name can be customised by deriving a customised class from web.database.object.Table and overriding the _relatedTableName() method of both tables.

>>> john = database['Person'].insert(firstName='John', surname='Smith')
>>> owen = database['Person'].insert(firstName='Owen', surname='Jones')
>>> 
>>> friendlyPlace = database['Address'].insert(firstLine='12 Friendly Place', postcode='MK4 1AB')
>>> crazyGardens  = database['Address'].insert(firstLine='3a Crazy Gardens', postcode='OX1 2ZX')
>>> greatRoad     = database['Address'].insert(firstLine='124 Great Road', postcode='JG6 3TR')
>>> 
>>> john.relate(friendlyPlace)
>>> owen.relate(greatRoad)
>>> crazyGardens.relate(john)
>>> 
>>> print john['addresses'].keys()
['MK4 1AB', 'OX1 2ZX']
>>> for address in john['addresses'].values():
...     print address['postcode']
...
MK4 1AB
OX1 2ZX
>>> print greatRoad['people'].keys()
['Owen']
>>> print owen['addresses']['JG6 3TR']['people'].keys()
['Owen']
>>> john['addresses']['MK4 1AB']['firstLine'] = 'The Cottage, 12 Friendly Place'
>>> print database['Person']['John']['addresses']['MK4 1AB']['firstLine']
The Cottage, 12 Friendly Place

The code should be fairly self-explainatory. We are inserting some different people and addresses into the table and the relating them to each other. Each row from each table can be related to as many other rows from the other table as you like. Or a row might not be related to another one at all.

It should be noted that you cannot set the values of multiple columns like the 'addresses' column directly. Instead you should set the values of each object induvidually.

>>> john['addresses'] = something # XXX Doesn't work!

database['Person']['John']['addresses']['MK4 1AB']['firstLine']

Here we are selecting all the addresses from the row 'John' from the 'Person' table and then selecting the first line of the address with postcode 'MK4 1AB'. It is actually possible to create circular references (although not very useful) as shown below.

>>> john == database['Person']['John'] == \
... database['Person']['John']['addresses']['MK4 1AB']['people']['John'] \
... == database['Person']['John']['addresses']['MK4 1AB']['people']['John'] \
... ['addresses']['MK4 1AB']['people']['John']
True

Just for interest here is how the tables look after running the example. You can see that the Address_Person table contains the rowids of the related people and addresses.

# Tables in the 'test' database
+----------------+
| Tables_in_test |
+----------------+
| Address        |
| Person         |
| Address_Person |
+----------------+

# The Person table
+-------+-----------+---------+
| rowid | firstName | surname |
+-------+-----------+---------+
| 1     | John      | Smith   |
+-------+-----------+---------+
| 2     | Owen      | Jones   |
+-------+-----------+---------+

# The Address table
+-------+------------------------------------+----------+
| rowid | firstLine                          | postcode |
+-------+------------------------------------+----------+
| 1     | The Cottage, 12 Friendly Place     | MK4 1AB  |
+-------+------------------------------------+----------+
| 2     | 3a Crazy Gardens                   | OX1 2ZX  |
+-------+------------------------------------+----------+
| 2     | 124 Great Road                     | JG6 3TR  |
+-------+------------------------------------+----------+

# The Address_Person table

+--------+-----------+
| people | addresses |
+--------+-----------+
| 1      | 1         | 
+--------+-----------+
| 2      | 2         |
+--------+-----------+
| 1      | 3         |
+--------+-----------+

It should be noted that each table can contain as many columns, multiple, related and single joins as you like.