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 <kevin...@gmail.com> 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.