[sqlalchemy] Re: Mapping special child rows
Okay, I have another question related to this.
Now that I have max_order defined, I want to do a query on it (give me
the users whose max_order==5). My code is
max_orders_by_user =
select([func.max(orders.c.order_id).label('order_id')],
group_by=[orders.c.user_id]).alias('max_orders_by_user')
max_orders =
orders.select(orders.c.order_id==max_orders_by_user.c.order_id).alias('max_orders')
mapper(User,
users,
properties={
'orders':relation(class_mapper(Order), backref='user'),
'max_order':relation(mapper(Order, max_orders,
non_primary=True), uselist=False),
'addresses':relation(mapper(Address, addresses), backref='user'),
})
It seemed like if I labeled the column something unique in
max_orders_by_user, then I should be able to use that in select_by, a
la
http://www.sqlalchemy.org/docs/datamapping.myt#datamapping_selectrelations_relselectby
So I changed that to max_order_id and tried
max_orders_by_user =
select([func.max(orders.c.order_id).label('max_order_id')],
group_by=[orders.c.user_id]).alias('max_orders_by_user')
max_orders =
orders.select(orders.c.order_id==max_orders_by_user.c.max_order_id).alias('max_orders')
# mapper as above
session.query(User).select_by(max_order_id=5)
and got
sqlalchemy.exceptions.InvalidRequestError: Cant locate property named
'max_order_id'
I did get it to work with
session.query(User).select(max_orders.c.order_id==5, from_obj=[max_orders])
Is there a way to do this with select_by?
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping special child rows
errr, the relations that mapper setup can feed into select_by would
include all the attributes you have on Order, which does not include
max_order_id. youve got two relations that both point to an Order
relation, so select_by is more or less out at that point since it can
only be given order_id, and it will just pick one or the other.
however, you might get away with it if you do it like this:
mapper(User,
users,
properties={
'orders':relation(class_mapper(Order), backref='user'),
'max_order':relation(mapper(Order, max_orders,
non_primary=True, properties={'max_order_id':synonym('order_id')}),
uselist=False),
'addresses':relation(mapper(Address, addresses),
backref='user'),
})
session.query(User).select_by(max_order_id=10)
just a guess.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping special child rows
Jonathan Ellis wrote:
o2 = orders.select().alias('o2')
max_orders = orders.select(orders.c.order_id==max_order_id).alias('max_orders')
youre not really showing me what you did there fully so its hard for me
to tell how you got that result.
- I take it SA doesn't really support subselects in WHERE clauses?
it does. the scalar flag currently only applies to select()
statements that are assembled into the column clause of another select
(i.e. the select statement's scalar value evaulates to one of the
result columns directly), so that isnt going to help here. also you
have that clever approach with the group_by user_id to get all the
max's in one shot, so you also arent looking for a correlated
expression here...(right ?) so you just take your max selectable and
join to its results:
max = select([func.max(orders.c.order_id).label('order_id')],
group_by=[orders.c.user_id]).alias('foo')
s = select(
[orders],
orders.c.order_id==max.c.order_id
)
SELECT orders.order_id, orders.user_id, orders.description,
orders.isopen
FROM orders, (SELECT max(orders.order_id) AS order_id
FROM orders GROUP BY orders.user_id) AS foo
WHERE orders.order_id = foo.order_id
the correlated version (which is what i normally would think
of...though the group_by thing is definitely better) looks like this:
o2 = orders.alias('o2')
max = select([func.max(o2.c.order_id)],
orders.c.order_id==o2.c.order_id)
s = select(
[orders],
orders.c.order_id==max
)
SELECT orders.order_id, orders.user_id, orders.description,
orders.isopen
FROM orders
WHERE orders.order_id = (SELECT max(o2.order_id)
FROM orders AS o2
WHERE orders.order_id = o2.order_id)
and in both cases here im thinking first of the actual SQL id want to
write, then i mentally translate that to an expression. we're getting
close to needing a next wave of documentation where i both really
nail all the little flags and switches like scalar=True, and also
come up with more of a repeatable methodology to get results (i.e.
always think from the literal SQL first; with ORM i always think of
result rows and not queries, etc.)
- Is there an alternative way to map max_order that I'm missing?
the mapping thing youre doing im not as confident in, since non_primary
mappers usually lead to issues. if youre looking to have the whole
thing eager load in one query (i.e. parent/child), i can see that not
working, since eager loading applies a lot of aliasing rules to
relationships (not to mention youd need at least a viewonly flag on
that relation). but you should definitely try it since im not really
sure.
*maybe* you could also just have the max query stuck into a normal
relation as the primaryjoin:
maxorder:relation(Order,
primaryjoin=orders.c.order_id==max.c.order_id, viewonly=True)
if the above two approaches fail, i would use a result-set mapping
(i.e. create a query that joins the Users and max Orders, send that to
instances(), then connect them together yourself afterwards).
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping special child rows
Jonathan Ellis wrote: Hmm, another undocumented option... :) How is a viewonly relation on the class different from a relation on a non_primary mapper? oh theyre both documented. a non_primary mapper means, you load instances from the DB using an alternate table/selectable. once those instances are loaded, changes to them are persisted via the primary mapper. things that can go wrong with non_primary mappers are the mapped columns not matching up, and/or the relationships on the primary/non_primary mappers not matching up. a relation with view_only typically uses the normal mapper for the class, but at the relationship level on the parent object, the relationship is non-persisted (i.e. you can append/delete from that relation collection without it affecting the flush()). view_only relations have even more problems than non-primary mappers since people are putting completely huge joins in these relations for which the lazyload and eagerload clause generators totally trip over (and im only willing to go so far in accomodating these clauses, since you can just use a separate property/result set mapping for very complex queries). Both the non_primary mapper and the viewonly relation work so far. Are either or both of these supposed to work enough that I can add a test for this? if theyre working for you, a. congrats! and b. yes, if they work for your particular clauses then they work. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
