[sqlalchemy] Re: How to model an odd many-to-many relationship?

[Gaetan de Menten]

On 3/20/07, Mike Kent [EMAIL PROTECTED] wrote:

 I have a many-to-many relationship between two entities, call them A
 and B.  This is easy enough to model in sqlalchemy using Elixir.
 However, to complicate things, I need an integer column somewhere
 called 'priority'.  In the relationship between an A and multiple Bs,
 I want the Bs to be ordered by the value of the 'priority' column.  In
 the relationship between a B and multiple As, the value of 'priority'
 is irrelevant.

 The problem is, I don't know what entity needs to have the 'priority'
 column in it, or how to model this in the relationship between A and
 B.  I thought that maybe the 'priority' column should be in the
 secondary table that handles the many-to-many relationship between A
 and B, but I'm not sure how to set that up, and I'm not sure that
 Elixir can handle that at all.

Basically Elixir doesn't handle that case. For now, your only option
is to make a separate entity for the intermediary table, which would
hold two belongs_to relationships, and use has_many relationships in
your existing entities (A and B) to that new entity, instead of using
has_and_belongs_to_many relationships.

Now on the SQLAlchemy's side, you can either use that solution too, or
use an AssociationProxy. See:

 http://www.sqlalchemy.org/docs/plugins.html#plugins_associationproxy

I personally feel the associationproxy should be better integrated
into the normal relation function (preferably as an option), but
Michael Bayer doesn't agree with me on this. My point is that
many-to-many relationship are already doing some kind of
associationproxy behind the scenes, so I don't see why you should use
an immensely different way to set things up simply because you have
extra fields in your intermediary table.

As for Elixir, I've been thinking to integrate the associationproxy
into the has_and_belongs_to_many relationship for some time now. I'll
do it if enough people are interested.

-- 
Gaëtan de Menten
http://openhex.org

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to model an odd many-to-many relationship?

[Mike Kent]

Gaetan,

Thanks for your response.  I'd like to follow up with two questions:

1. I understand what you mean about the current way I'd have to model
this relationship
in Elixir.  This would allow me full control over the intermediate
table, which means I could
put my 'priority' field in it.  However, I believe this will also
prevent me from getting 'all Bs for a given A, in priority order',
by simply accessing the correct property in an A.  Instead, when
accessing that property, I could get all occurances of the
intermediate table for an A.  Can you give me some idea of how I would
set up a select that would give me all Bs for an A, in 'priority'
order?

2. When you talk about 'Now on the SQLAlchemy side', it makes me thing
that in addition to setting up the relationships in Elixir as you
specified, I would also need to do something directly in SQLAlchemy.
Is that what you meant, or am I misinterpreting you?

Finally, I do hope you will add the capability for finer-grained
control over the intermediate table of a many-to-many relationship to
Elixir.  I would think that this kind of ordered many-to-many
relationship is a prevalent enough use case to warrant it.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---