Thanks. I have it working for the Marker to Reference_Assoc relationship:
Marker.referenceAssocs = relationship("ReferenceAssoc",
primaryjoin="""and_(ReferenceAssoc._mgitype_key==2,
ReferenceAssoc._object_key==Marker._marker_key)""",
foreign_keys=[Marker._marker_key],
backref="marker",
uselist=True,
)
However, that is just a one to many relationship. I am not certain how to
expand that to define Marker.references. Which essentially translates to
Marker => ReferenceAssoc => Reference.
I am not sure how many relationships I need and on which classes to define
them.
On Wednesday, March 27, 2013 12:48:23 PM UTC-4, Simon King wrote:
>
> On Wed, Mar 27, 2013 at 4:13 PM, Kevin S <kevin...@gmail.com <javascript:>>
> wrote:
> > Setup: I have been learning SQL Alchemy to build a prototype (proof of
> > concept) Flask app for our internal website. We want to replace our
> current
> > site, which is made entirely of slow python CGIs and raw SQL. Our
> database
> > (Postgres) is fairly large, but has some unusual table relationships,
> and is
> > highly normalized in regards to data entry (BUT, I am only interested in
> a
> > read only interface at the moment. I.e. queries and summaries). My goal
> is
> > to see how well SQL Alchemy can handle some of these relationships.
> Whether,
> > it is being able to fully define the relationships in the model, or if
> we
> > have to define the basic objects and use join syntax at query time, I
> just
> > want to know what's possible.
> >
> > Ok, so here is one example that I haven't been able to figure out the
> > correct way to do (There is a lot you can do in SQLAlchemy!). I won't go
> > into all my failed attempts.
> > We have a join table (several like it actually) that holds many
> different
> > types of object relationships in it. You specify the correct join
> conditions
> > using a type key.
> > Say these are the tables:
> > Marker, Allele, Reference_Assoc, Reference.
> >
> > You get markers for a reference by doing "Reference.ref_key =
> > Reference_Assoc.ref_key AND Reference_Assoc.object_key=Marker.marker_key
> AND
> > Reference_Assoc.type_key=1".
> > However you can also get alleles for a reference by doing
> "Reference.ref_key
> > = Reference_Assoc.ref_key AND
> Reference_Assoc.object_key=Allele.allele_key
> > AND Reference_Assoce.type_key=2"
> >
> > I don't know a whole lot about database patterns, so I don't know what
> this
> > relationship would be called. However, this database schema is not
> changing
> > anytime soon, so I'm stuck with it.
> >
> > So.. Markers and References, or Alleles and References, are both many to
> > many relationships through the Reference_Assoc table. Is it possible to
> > represent this in SQL Alchemy mappings, where you could set up a mapping
> to
> > have Marker.references as a property (and the backref
> Reference.markers)? It
> > would be really nice to be able to define this relationship in an easy
> way,
> > because we have over 30 different types of objects that can be
> associated
> > this way.
> >
> > P.S. I have been using the declarative method of defining tables, but I
> can
> > go a different route if that won't work.
> >
>
> For the read-only case this should be fairly simple - you can define
> whatever join criteria you want when creating a relationship. See the
> examples at
> http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#specifying-alternate-join-conditions.
>
>
>
> If you decide in the future to make this a writable interface as well,
> I suspect you will have to work a bit harder. You'll probably want to
> map a class to the Reference_Assoc table (optionally using
> single-table inheritance so that you have a subclass per type_key)
>
> Hope that helps,
>
> Simon
>
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
