[orm-devel] many2many extension

[Ross J. Reedstrom]

Hey all - 
I'm playing with ORM a bit, to see how it might fit into our Postgresql/Zope
setup. One thing I'm looking at is the many2many relationship. As written,
it has a builtin assumption: that your 'matrixtable' (I usually
call this a linking table) hold the primary keys of the two linked tables.

In my case, we've got a structure where we're grouping together collections
of people, and reusing the groupings, where possible. Here's an example:

    modules (moduleid serial,
             name text,
             version int,
             authorgroupid int,
	     maintainergroupid int)

    persons (personid serial,
            name text,
	    login text)

    groups (groupid int,
            personid int)

In this case, we've two different many2many relationships between modules
and people: authors, and maintainers. Since we have many modules, and
many authors, to optimize storage space we reuse a groupids, when we can
(same set of people)

So, to retrieve all the authors for a given module (1234), it would be
something like:

SELECT name from modules, persons, groups where moduleid=1234 and
authorgroupid=groupid and groups.personid = persons.personid

For ORM, this means modifying the many2many class to take another
parameter (I've called it myKey) which is the SQL column name in the
parent table that hold the key that is stored in the matrix table.

So, here's my problem: how do I tweak ORM to retrieve this column type?
It's basically a many2many, but I need to retrieve the 'myKey' column
when the parent table tuples are pulled, and then us that value when
the child tuples are pulled.

The last part is here: (relationships.many2manyColumn._init_, ~ line 540)

existing code says:

            where = "%s.%s = %s AND %s.%s = %s.%s " % \
                    (self._column.matrixTable(),
                     self._column.myColumn(),
                     self._column.dbObj().primaryKeyValue().format(), 
                     self._column.matrixTable(),
                     self._column.childsColumn(),
                     self._column.childClass().relation(),
                     self._column.dbObj().primaryKey)

There's a bug here, BTW: the last line should be (it's slipped through
because all your primaryKeys are named 'id'):

                     self._column.childClass().primaryKey)


I need something like:

           where = "%s.%s = %s AND %s.%s = %s.%s " % \
                    (self._column.matrixTable(),
                     self._column.myColumn(),
                     self._column.myKey(), #FIXME need _value_ of myKey
                     self._column.matrixTable(),
                     self._column.childsColumn(),
                     self._column.childClass().relation(),
                     self._column.childClass().primaryKey)


Any suggestions?

Ross
-- 
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Executive Director                                  phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
Rice University MS-39
Houston, TX 77005