Also, is it appropriate to define a backref here? It returns wrong data.
When I get a ReferenceAssoc object and get its marker, it generates a query
with a clause like:
"reference_assoc._mgitype_key = @_mgitype_key_1 AND @param_1 =
marker._marker_key"
and these params : {'@_mgitype_key_1': 2, '@param_1': 215}
That does not make sense as SQL and just returns a random marker.
On Wednesday, March 27, 2013 1:05:26 PM UTC-4, Kevin S wrote:
>
> 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 <[email protected]> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
