[Twisted-Python] enterprise.row and two-way relationships

[Sean Riley]

well, i can think of two ways - but neither are particularly good.

You could create RowObject classes for each of the three tables - account,
contact, and rel  - and define foreign keys with the "rowForeignKey"
attributes between the tables.

Then use loadObjectsFrom to load objects from the rel table, and have those
"relRow" objects auto-load their children - which would be the rows from the
contacts table... This is not terribly efficient as it doesn't take
advantage of sql joins or set operations at all, but it should work.

For the second way, create a view on the rel and contacts tables:

create view account_contacts as
select rel.account_id, contact.contact_id, contact.name
from contact, rel
where rel.contact_id = contact.contact_id;

and create a rowObjectClass for the view. This would allow the rows to be
loaded, but updates and inserts could be a problem. Some databases cant do
updates or inserts to views... Others require rules or triggers to do it.


Dealing with many-to-many relationships like this is really outside of the
immediate scope of the current row code. Its a pretty common problem though,
so it is a logical place for the next extensions.  I'm not yet sure how to
make a good interface for this. I am reluctant to introduce more rowClass
attributes without some better validation or generation scheme - it is
already tedious and error-prone enough to type in all the columns, types and
relationships.

An SQL parser may be one way. If there was some way to generate
rowObjectClasses for queries dynamically, with functionality similar to the
way cursors work, that would be a generally useful solution....

Any thoughts on extending the Row API are welcome :)


-----Original Message-----
From: twisted-python-admin at twistedmatrix.com
[mailto:twisted-python-admin at twistedmatrix.com]On Behalf Of Justin Ryan
Sent: Sunday, October 20, 2002 2:45 PM
To: twisted-python at twistedmatrix.com
Subject: Re: [Twisted-Python] enterprise.row and two-way relationships


On Sun, 2002-10-20 at 13:50, Ken Kennedy wrote:
> On Sat, Oct 19, 2002 at 06:21:32PM -0500, Justin Ryan wrote:
> > Hello..
> >
> > To solve this problem, I've got a 'rel' table which keeps relationships
> > between account ids and contact ids, so if I want all of the contacts
> > for account x I simply pull all of the rows out of the 'rel' table whose
> > accountId is x, and then individually step through them and extract the
> > contacts with the corresponding accountIds..
> >
> > I don't think there is a problem with the way I'm doing this, or a
> > better way to do this (though I'd be tickled if someone could point me a
> > better way), but in any case there does not seem to be an (obvious) way
> > to handle this with RowObjects..
>
> It's a little unclear, but the "pull all of the rows out..and then
> individually step through them" seems to suggest you're making two
> queries to the db. If that's the case,then yeah, there's a better
> way. If you know the accountID (from some previous query), just join
> rel to contacts:
>
> "select contact_info from contacts inner join rel on (rel.contactid =
> contacts.contactid) where rel.accountid = whatever"
>
> If you only know account name info, use:
>
> "select contact_info from
>        accounts inner join rel on (rel.accountid = accounts.accountid)
>        inner join
>        contacts on (contacts.contactid=rel.contactid)
>        where account.accountname = 'whatever'
> "
>
> Either way, you should be getting back what you need in one
> query. Does that make sense?
>

Certainly.., and it's not that far off from what I was doing.. except it
does seem a bit off in that no info is actually being pulled from the
account table - and if it were, it would be pulled multiple times (if i
want to pull the account name and account id, and then a list of
contacts).

the big important question here is ('scuse my german)... *drumroll*

how the fuck do I do this with twisted.enterprise.row? ;)

Unfortunately, the documentation for row seems pretty sketchy and shows
a direct parent-child relationship (room->furniture)..

it seems very unimportant what the underlying SQL does if row is
supposed to abstract it anyway..

any idears?

Thanks again!

-Justin