[Zope] ZODB for many-to-many relation

[Steve Spicklemire]

Hi Milos,

I'm not sure if I love it yet (I'm still playing) but this is a solution 
I've put into practice on one project that may work for you. I've 
defined two ZClasses, 'Relationship' and 'RelationshipItem'. I'm using 
ZPatterns, but I don't think that this affects the validity of the 
solution, it just allows me to switch to a RDBMS tomorrow if that turns 
out to be helpful. ;-)

Relationship is really just a handy target. Relationship objects are 
created with unique ids, and they have a description property/field that 
can be used to describe particulars of any single relationship.

RelationshipItems have several attributes: riType (this is the meta_type 
of the item involved in the relationship), originalID (the original id 
of the item in this relationship), relationshipID (the id of the 
relationship object) to which this relationshipItem is related, and 
finally relationshipRole (the role played by this item in this 
relationship).

Now... in my Application I have 'mediaItems', 'Artists', and 
'Locations'. An actual relationship consists of a set of zero or more 
items of these three types (clearly more types could be added without 
changing the system). A catalog is used (which would/could be replaced 
by SQL if that becomes a future direction.) To create a relationship 
between objects I (1) create a new Relationship object, and (2) create a 
RelationshipItem object for each item in the relationship. Here is an 
example method that gets Artists that have some relationship to a 
particular mediaItem:

## Script (Python) "getArtistIDsForMediaItem"
##bind container=container
##bind context=context
##bind namespace=
##bind script=script
##bind subpath=traverse_subpath
##parameters=mediaItemID
##title=
##
theCatalog = container.Catalog
theArtistIDs = []

theMediaItemBrains = theCatalog(riType='mediaItem', 
originalID=mediaItemID)

for mib in theMediaItemBrains:

	theRelationID = mib.relationshipID
	artistBrains = theCatalog(riType='Artist', 
relationshipID=theRelationID)
	for ab in artistBrains:
		theArtistIDs.append(ab.originalID)
		
return theArtistIDs


It takes one catalog call to get all the Relationships for a particular 
mediaItem, and for each of those, I do   one catalog call to find all 
the Artists in that relationship. With SQL you could do the whole lot on 
one call. With ZPatterns it's easy to use methods like this to add this 
method to the mediaItem object as an attribute called 'myArtistIDs' so 
that the programmer doesn't need to know *how* they are gotten, just 
that they are there.

-steve

On Monday, June 4, 2001, at 12:06 PM, Milos Prudek wrote:

> I wonder if ZODB is suitable for the following problem, which I'll
> describe in relational terms:
>
> There are articles and writers (authors). Each article has at least one
> writer, but it can have more than one writer. I need to list all
> articles of a given writer, and all writers of a given article.
>
> A pure relation database solution would be as follows:
>
> To escape putting list of writers in 'articles' table, there is an
> 'article-writer' table, called 'aw'. It contains valid combination of
> writers & articles.
>
> aw table has these columns: a, w.
> articles table has these columns: a,other-data
> writers table has these columns: w,other-data
>
> To list all articles of a writer No.1 :
> SELECT aw.a, articles.other-data
> FROM aw, articles, writers
> WHERE
>   aw.w=1
>     AND
>   writers.w=1
>     AND
>   articles.a=aw.a
>
> To list all writers of article No.2:
>
> SELECT
> FROM aw,articles,writers
> WHERE
>   aw.a=2
>     AND
>   article.a=2
>     AND
>   writers.w=aw.w
>
> Is there an OO solution to this situation?
>
> --
> Milos Prudek, confused ZODB newbie
>
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists -
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )