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.
