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.
