Ok just discovered http://docs.sqlalchemy.org/en/rel_0_9/core/sqlelement.html#sqlalchemy.sql.expression.tuple_ for IN clause on multiple columns, however that's the lesser solution
On Monday, May 5, 2014 1:26:24 AM UTC+2, Dimitris Theodorou wrote: > > Hi, > > I've spent a good deal of time trying to create a (viewonly) relationship > between the following two entities with limited success. My model is like > this: > > class Parent(): > id = Column(Integer, primary_key=True) > transaction_id = Column(Integer, primary_key=True) > > class Child(): > id = Column(Integer, primary_key=True) > transaction_id = Column(Integer, primary_key=True) > parent_id = Column(Integer) #NOT an explicit foreign key > > The unusual conditions: 1) composite primary keys, and 2) the relation of > child to parent is resolved by the following query: > > Assuming a parent with id=2 and transaction_id=3, parent.children should > be resolved by > > SELECT * > FROM children > WHERE > parent_id = 2 > AND transaction_id <= 3 > AND (id, transaction_id) IN ( > SELECT id as id, max(transaction_id) AS max_transaction > FROM children > WHERE transaction_id <= 3 > GROUP BY id > ) > > In other words, for each child the parent_id is resolved like a regular > foreign key, while transaction id is the maximum found as long as it is > lower than the parent's transaction id. (a type of query illustrated at > http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column > ) > > There is also an equivalent query with join > > SELECT * > FROM children > JOIN ( > SELECT id as id, max(transaction_id) AS max_transaction > FROM children > WHERE transaction_id <= 3 > GROUP BY id > ) AS children_max_transactions > ON children.id = children_max_transactions.id > AND children.transaction_id = children_max_transactions.max_transaction > WHERE > parent_id = 2 > AND transaction_id <= 3 > > > Now I am completely stupified as to how to express the JOIN query (best of > the two) using relationship's primaryjoin, secondary and secondaryjoin. As > for the first, can sqlalchemy express IN clause with multiple columns? > > I know I can express these attributes as regular @property 's but I need > to use eager loading with them. Which also raises the question, should > viewonly relationships support arbitrary queries to gain the advantage of > all the loading strategies? (or should query-returning @property 's be > supported in loading strategies) > > > -- 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. For more options, visit https://groups.google.com/d/optout.
