Excellent, that worked!
It took a little tweaking, but I figured out how to define the foreign keys.
Note: this syntax is using Flask-SQLAlchemy so there's no metadata defined.
reference_assoc = db.Table('reference_assoc',
db.Column('object_key',db.Integer()),
db.Column('refs_key',db.Integer,db.ForeignKey('reference.refs_key')),
db.Column('type_key',db.Integer()),
)
Marker.references = db.relationship("Reference",
secondary=reference_assoc,
primaryjoin=db.and_(Marker.marker_key==reference_assoc.c.object_key,
reference_assoc.c.type_key==1),
secondaryjoin=(reference_assoc.c.refs_key==Reference.refs_key),
foreign_keys=[Marker.marker_key,Reference.refs_key],
backref="markers",
)
One minor peculiarity is that I had to add a ".c" after reference_assoc to
access each column. Otherwise I get this error:
AttributeError: 'Table' object has no attribute 'object_key'
Anyway, thanks for your help.
On Wed, Mar 27, 2013 at 5:46 PM, Simon King <si...@simonking.org.uk> wrote:
> 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.
>
>
>
--
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.
