Ok, I'm sorry to be confusing. But this backref thing, it isn't returning
"random" results, but the join condition is all wrong.
It is taking a ReferenceAssoc object with type_key = 11 and object_key=215,
and making the where clause I showed.
So I get back a marker with marker_key 215, but really that referenceAssoc
is not for a marker but for some other type.
It should not bring back any markers.
On Wednesday, March 27, 2013 1:14:43 PM UTC-4, Kevin S wrote:
>
> 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.
