[sqlalchemy] A complicated composite secondary join relationship() property involving four tables

2014-10-20 Thread Peter Waller 
Hi All,

I am wondering if it is possible to describe in sqlalchemy the
following relationship with the new features introduced since 0.9.2.
I've been reading this document and it looks close to what I'm trying
to achieve:

 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins

As does this previous mailing list post:

 https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion

There is a pre-existing structure to deal with. I would rather not
change the structure if it can be avoided, but may have to resort to
doing so. Here are the tables:

TableName (column, ...)

 PersonAction (PersonID, ActionID)
 PersonUnit (PersonID, UnitID)
 DepartmentUnit (UnitID, DepartmentID)
 ActionMeaning (DepartmentID, ActionID)

It would be nice to be able to go from an ActionMeaning to a
PersonAction and back again. To do that, a conversion between
DepartmentName and DepartmentID needs to happen.

Here are a few of the relationships:

One PersonAction: one ActionMeaning
One ActionMeaning: many PersonAction
One PersonUnit many  PersonAction
One PersonUnit one DepartmentUnit
One DepartmentID: many UnitID
  (therefore one ActionMeaning: many DepartmentUnit)
One ActionMeaning: Many PersonUnit (via DepartmentUnit)

I'm using the ORM and I would like to be able to write a property on
the PersonAction which allows me to efficiently get to the
ActionMeaning (preferably joinedload'ed), and likewise I would like to
be able to go from an ActionMeaning to all of the related
PersonActions.

Is this possible? Or are there alternative tasteful strategies for
dealing with this tricky structure?

Life would be much easier if I had the DepartmentID on the PersonUnit...

Keywords to help anyone searching in the future:

Composite relationship between multiple tables
Joining multiple tables in a relationship
relationship secondary join primaryjoin secondaryjoin

Thanks,

- Peter

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] A complicated composite secondary join relationship() property involving four tables

2014-10-20 Thread Michael Bayer 

 On Oct 20, 2014, at 1:14 PM, Peter Waller pe...@scraperwiki.com wrote:
 
 Hi All,
 
 I am wondering if it is possible to describe in sqlalchemy the
 following relationship with the new features introduced since 0.9.2.
 I've been reading this document and it looks close to what I'm trying
 to achieve:
 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins
 
 As does this previous mailing list post:
 
 https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion
 
 There is a pre-existing structure to deal with. I would rather not
 change the structure if it can be avoided, but may have to resort to
 doing so. Here are the tables:
 
 TableName (column, ...)
 
 PersonAction (PersonID, ActionID)
 PersonUnit (PersonID, UnitID)
 DepartmentUnit (UnitID, DepartmentID)
 ActionMeaning (DepartmentID, ActionID)

So as far as the patterns at 
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html, this one 
qualifies for 
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper.
  The reason is because PersonAction has a direct FK to ActionMeaning 
(ActionID) as well as towards elements on what would be “secondary” here, 
PersonUnit/DepartmentUnit.  The criteria for that is: we seek to join from A 
to B, making use of any number of C, D, etc. in between, however there are also 
join conditions between A and B directly.”

So you map ActionMeaning using a non-primary mapper to a join of ActionMeaning, 
DepartmentUnit, and PersonUnit, then PersonAction can relate to this mapper 
directly.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] A complicated composite secondary join relationship() property involving four tables

2014-10-20 Thread Peter Waller 
Outstanding! My puny mind was just beginning to tickle this
documentation and was starting to get the idea that this is the
right direction. Good to know I'm on track.

Is it also possible that having a class which maps to a join* will
help? Then rather than having PersonAction and PersonUnit I would just
have Person. Would that simplify the relationships?

I'll have a play around with this and see where I get.

Thanks,

- Peter

* http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#maptojoin

On 20 October 2014 18:26, Michael Bayer mike...@zzzcomputing.com wrote:

 On Oct 20, 2014, at 1:14 PM, Peter Waller pe...@scraperwiki.com wrote:

 Hi All,

 I am wondering if it is possible to describe in sqlalchemy the
 following relationship with the new features introduced since 0.9.2.
 I've been reading this document and it looks close to what I'm trying
 to achieve:

 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins

 As does this previous mailing list post:

 https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion

 There is a pre-existing structure to deal with. I would rather not
 change the structure if it can be avoided, but may have to resort to
 doing so. Here are the tables:

 TableName (column, ...)

 PersonAction (PersonID, ActionID)
 PersonUnit (PersonID, UnitID)
 DepartmentUnit (UnitID, DepartmentID)
 ActionMeaning (DepartmentID, ActionID)

 So as far as the patterns at 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html, this one 
 qualifies for 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper.
   The reason is because PersonAction has a direct FK to ActionMeaning 
 (ActionID) as well as towards elements on what would be “secondary” here, 
 PersonUnit/DepartmentUnit.  The criteria for that is: we seek to join from A 
 to B, making use of any number of C, D, etc. in between, however there are 
 also join conditions between A and B directly.”

 So you map ActionMeaning using a non-primary mapper to a join of 
 ActionMeaning, DepartmentUnit, and PersonUnit, then PersonAction can relate 
 to this mapper directly.

 --
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] A complicated composite secondary join relationship() property involving four tables

2014-10-20 Thread Peter Waller 
This is how far I got, before running into a wall:

 j = join(ActionMeaning, DepartmentUnit).join(PersonUnit)
 disambiguation = {
   UnitID: [j.c.PersonUnit_UnitID,  j.c.DepartmentUnit_UnitID],
   DepartmentID: [j.c.DepartmentUnit_DepartmentID,
j.c.ActionMeaning_DepartmentID],
 }
 ActionMeaningToPerson = mapper(ActionMeaning, j, non_primary=True,
properties=disambiguation)
 PersonAction.meaning = relationship(ActionMeaningToPerson)

ActionMeaning has a DepartmentID -
ForeignKey(DepartmentUnit.DepartmentID)
PersonUnit has UnitID - ForeignKey(DepartmentUnit.UnitID)
PersonAction has PersonID - ForeignKey(PersonUnit.PersonID)

This actually does something when I look at PersonAction.meaning, except
unfortunately I cannot yet figure out where to express the constraint
ActionMeaning.ActionID == PersonAction.ActionID, so I get back all
ActionMeanings with a matching DepartmentID, and ActionID of various sorts.

I've tried expressing it as the relationship(primaryjoin=), but this gave
me:


ArgumentError: Could not locate any relevant foreign key columns for
primary join condition 'ActionMeaning.ActionID =
PersonAction.ActionID' on relationship PersonAction.meaning.  Ensure
that referencing columns are associated with a ForeignKey or
ForeignKeyConstraint, or are annotated in the join condition with the
foreign() annotation.


Any hints?

Thanks again!

On 20 October 2014 18:26, Michael Bayer mike...@zzzcomputing.com wrote:


  On Oct 20, 2014, at 1:14 PM, Peter Waller pe...@scraperwiki.com wrote:
 
  Hi All,
 
  I am wondering if it is possible to describe in sqlalchemy the
  following relationship with the new features introduced since 0.9.2.
  I've been reading this document and it looks close to what I'm trying
  to achieve:
 
 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins
 
  As does this previous mailing list post:
 
  https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion
 
  There is a pre-existing structure to deal with. I would rather not
  change the structure if it can be avoided, but may have to resort to
  doing so. Here are the tables:
 
  TableName (column, ...)
 
  PersonAction (PersonID, ActionID)
  PersonUnit (PersonID, UnitID)
  DepartmentUnit (UnitID, DepartmentID)
  ActionMeaning (DepartmentID, ActionID)

 So as far as the patterns at
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html, this one
 qualifies for
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper.
 The reason is because PersonAction has a direct FK to ActionMeaning
 (ActionID) as well as towards elements on what would be “secondary” here,
 PersonUnit/DepartmentUnit.  The criteria for that is: we seek to join from
 A to B, making use of any number of C, D, etc. in between, however there
 are also join conditions between A and B directly.”

 So you map ActionMeaning using a non-primary mapper to a join of
 ActionMeaning, DepartmentUnit, and PersonUnit, then PersonAction can relate
 to this mapper directly.

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] A complicated composite secondary join relationship() property involving four tables

2014-10-20 Thread Michael Bayer 

 On Oct 20, 2014, at 3:23 PM, Peter Waller pe...@scraperwiki.com wrote:
 
 This is how far I got, before running into a wall:
 
  j = join(ActionMeaning, DepartmentUnit).join(PersonUnit)
  disambiguation = {
UnitID: [j.c.PersonUnit_UnitID,  j.c.DepartmentUnit_UnitID],
DepartmentID: [j.c.DepartmentUnit_DepartmentID, 
  j.c.ActionMeaning_DepartmentID],
  }
  ActionMeaningToPerson = mapper(ActionMeaning, j, non_primary=True, 
  properties=disambiguation)
  PersonAction.meaning = relationship(ActionMeaningToPerson)
 
 ActionMeaning has a DepartmentID - ForeignKey(DepartmentUnit.DepartmentID)
 PersonUnit has UnitID - ForeignKey(DepartmentUnit.UnitID)
 PersonAction has PersonID - ForeignKey(PersonUnit.PersonID)
 
 This actually does something when I look at PersonAction.meaning, except 
 unfortunately I cannot yet figure out where to express the constraint 
 ActionMeaning.ActionID == PersonAction.ActionID, so I get back all 
 ActionMeanings with a matching DepartmentID, and ActionID of various sorts.
 
 I've tried expressing it as the relationship(primaryjoin=), but this gave me:
 
 
 ArgumentError: Could not locate any relevant foreign key columns for primary 
 join condition 'ActionMeaning.ActionID = PersonAction.ActionID' on 
 relationship PersonAction.meaning. 

your primaryjoin has to be a lot more than that.   it’s at least three 
different column combinations joined together by AND, so you’d be using and_() 
and within that ActionMeaningToPerson.x == PersonAction.y, 
ActionMeaningToPerson.q == PersonAction.p, etc.  (x/y/q/p are the columns you 
have there, DepartmentID, UnitID, etc.).  Everything should be in terms of 
those two classes.   ActionMeaningToPerson is like a big horizontal table now.

as for foreign, this usually has to be finessed in this kind of case.  if this 
is one-to-many, then I’d put the foreign() annotation on the 
ActionMeaningToPerson side, on those columns that act like a foreign key.   See 
the second part of 
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#creating-custom-foreign-conditions
 
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#creating-custom-foreign-conditions
 for what that looks like.



 Ensure that referencing columns are associated with a ForeignKey or 
 ForeignKeyConstraint, or are annotated in the join condition with the 
 foreign() annotation.
 
 
 Any hints?
 
 Thanks again!
 
 On 20 October 2014 18:26, Michael Bayer mike...@zzzcomputing.com 
 mailto:mike...@zzzcomputing.com wrote:
 
  On Oct 20, 2014, at 1:14 PM, Peter Waller pe...@scraperwiki.com 
  mailto:pe...@scraperwiki.com wrote:
 
  Hi All,
 
  I am wondering if it is possible to describe in sqlalchemy the
  following relationship with the new features introduced since 0.9.2.
  I've been reading this document and it looks close to what I'm trying
  to achieve:
 
  http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins
   
  http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins
 
  As does this previous mailing list post:
 
  https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion 
  https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion
 
  There is a pre-existing structure to deal with. I would rather not
  change the structure if it can be avoided, but may have to resort to
  doing so. Here are the tables:
 
  TableName (column, ...)
 
  PersonAction (PersonID, ActionID)
  PersonUnit (PersonID, UnitID)
  DepartmentUnit (UnitID, DepartmentID)
  ActionMeaning (DepartmentID, ActionID)
 
 So as far as the patterns at 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html, this one 
 qualifies for 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper
  
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper.
   The reason is because PersonAction has a direct FK to ActionMeaning 
 (ActionID) as well as towards elements on what would be “secondary” here, 
 PersonUnit/DepartmentUnit.  The criteria for that is: we seek to join from A 
 to B, making use of any number of C, D, etc. in between, however there are 
 also join conditions between A and B directly.”
 
 So you map ActionMeaning using a non-primary mapper to a join of 
 ActionMeaning, DepartmentUnit, and PersonUnit, then PersonAction can relate 
 to this mapper directly.
 
 --
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com 
 mailto:sqlalchemy%2bunsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com 
 mailto:sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy 
 http://groups.google.com/group/sqlalchemy.
 For more options,