When you model a many-to-many relationship in SQLAlchemy, you have a choice whether or not to map a class to the intermediate table. Generally, you only need to map that table if you want to get instances back from the ORM that represent those rows (presumably so that you can manipulate the values). When you do this, you don't really have a many-to-many relationship any more. Instead, you have a one-to-many relationship on one side of the intermediate class, and a many-to-one on the other side. SA refers to this pattern as an Association Object (http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#association-object)
In your case (as long as you are staying read-only), I don't think there is any need to map the intermediate table. Your code would look more like the example at http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#many-to-many, where the Reference_Assoc table is a Table instance, rather than a mapped class. Your Marker.references relationship would then look something like this: reference_assoc = Table('reference_assoc', Base.metadata, # reference_assoc columns go here ) Marker.references = relationship( Reference, secondary=reference_assoc, primary_join=and_(Marker.marker_key == reference_assoc.object_key, reference_assoc.type_key == 1), secondary_join=(reference_assoc.ref_key == Reference.ref_key), backref='markers', ) (That almost certainly won't work first time, but if you let us know what the errors are we'll try to help) Simon On 27 Mar 2013, at 17:28, Kevin S <kevinrst...@gmail.com> wrote: > 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 <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. > > -- 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.
