5 Relations and PyDO

The way you do relations with PyDO is with methods. For example, if we had a Files class which had an field OWNER_ID which was a foreign key to the USERS table, we could write a method for the Users object like this (a one to many relation):

    def getFiles(self):
        return Files.getSome(OWNER_ID = self['OID'])

The getSome static method, given fields in the object will generate a where clause with those fields and return a list of objects, each of which representing one row.

If Users had an One to One relationship with Residence, we could write a method to get it (presuming Residence, again has a foreign key to the USERS table in a column/field named OWNER_ID):

    def getResidence(self):
        return Residences.getUnique(OWNER_ID = self['OID'])

The getUnique static method is similar to getSome except that it will return only one row or None. It uses the unique attribute (here on the Residences object) to determine how to get a unique row. If you don't specify any identifying rows, it will raise an exception saying "No way to get a unique row", or in the case that it mysteriously finds more than one row, will raise a similar exception.

To do Many To Many relations, things are a bit more interesting. Since there may or may not be an object that represents the pivot table (or linkage table) that links the two tables together, and you probably wouldn't want to do the work to traverse all of them anyhow, there is a joinTable method which simplifies the work.

Say there is a Groups entity and a table USERS_TO_GROUPS which is the pivot table and has two columns, USER_ID and GROUP_ID (foriegn keyed as appropriate). You'd write a method getGroups as such:

    def getGroups(self):
        return self.joinTable('OID', 'USERS_TO_GROUPS', 'USER_ID',
                              'GROUP_ID', Groups, 'OID')

What this will do is do the join across the USERS_TO_GROUPS table to the table that the Groups object corresponds to. The parameters (matched up to the arguments supplied above) are:

thisAttributeNames: 'OID' attribute(s) in current object to join from
pivotTable: 'USERS_TO_GROUPS' pivot table name
thisSideColumns: 'USER_ID' column(s) that correspond to the foriegn key column to myAttributeName.
thatSideColumns: 'GROUP_ID' column(s) that correspond to the foriegn key column to thatAttributeName.
thatObject: Groups the destination object.
thatAttributeNames: 'OID' see thatSideColumns.

If in the case you want to do things like ordering and such on a many to many relation, you can use the joinTableSQL function (takes the same arguments) to get the sql and value list to use. From there you can add to the generated sql statement things like ORDER BY FOOTABLE.BAZCOLUMN and such. From there you use the dbi's execute function to execute the query and subsequently construct the objects.