On Wed, Mar 27, 2013 at 4:13 PM, Kevin S <kevinrst...@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.
