> 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, visit https://groups.google.com/d/optout
> <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
> <mailto:sqlalchemy+unsubscr...@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, visit https://groups.google.com/d/optout
> <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.
