Say I have tables corresponding to users and orders from test/tables.py.
I have dozens or hundreds of orders per user. I want to show a list of
users with their most recent order, but because I have so many orders
doing an eager load to orders is a bad solution. Having a manually
defined python property to pull in the most recent order is also bad
because I only want to do a single query.
So, I tried this
mapper(User,
users,
properties={
'orders':relation(mapper(Order, orders), backref='user'),
'max_order':relation(mapper(Order, max_orders, non_primary=True)),
'addresses':relation(mapper(Address, addresses), backref='user'),
})
But that's about as far as I got -- I couldn't come up with a max_orders
select that would work. First I tried
max_order_id = select([func.max(orders.c.order_id)],
group_by=[orders.c.user_id], scalar=True)
max_orders = orders.select(orders.c.order_id==max_order_id).alias('max_orders')
but max_orders.select() generated
SELECT orders.order_id, orders.user_id, orders.description, orders.isopen
FROM orders
WHERE orders.order_id = (SELECT max(orders.order_id) GROUP BY orders.user_id)
which selected every order rather than just orders 4 and 5. (The
subselect, with scalar=True removed, correctly selected those IDs.)
So I thought maybe adding an alias would un-confuse the subselect-in-where:
o2 = orders.select().alias('o2')
max_orders = orders.select(orders.c.order_id==max_order_id).alias('max_orders')
but this made negative progress:
sqlalchemy.exceptions.SQLError: (OperationalError) no such column: orders.order_
id 'SELECT orders.order_id, orders.user_id, orders.description, orders.isopen
FROM orders
WHERE orders.order_id = (SELECT max(o2.order_id)
FROM (SELECT orders.order_id AS order_id, orders.user_id AS user_id,
orders.description AS description, orders.isopen AS isopen) AS o2
GROUP BY o2.user_id)' []
So:
- I take it SA doesn't really support subselects in WHERE clauses?
- Is there an alternative way to map max_order that I'm missing?
(I could work around the subselect problem with a postgresql function,
but I seem to run into a lot of places to use this kind of
optimization so I'd prefer to avoid that.)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
