that's what i have in bitemporal queries.
u need a groupby and subquery/ies.
something along
subs = select(
[C.id.label('cid'), C.d_id.label('did'),
func.max(C.value).label('cvalue')]
).group_by( C.id )
giving the max cid/cvalues, and then somehow join Ds with that.
D.query( ...).filter( d.id == subs.c.did )
it depends, u may be able to approach it the other way, like
query(C)... .join(D)
don't take the aboves as solution, but rather as example of the
direction...
http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/timed/
www.svilendobrev.com
ciao
svil
On Friday 10 April 2009 13:13:26 Joril wrote:
> Hi everyone!
> I'm trying to concoct a somewhat complicated query via Query API..
> The situation is the following:
>
> - A many-to-one relation between classes C and D
> - Class C has an attribute "value"
>
> My objective is to retrieve the ids of a left outer join between D
> and C (so, all the Ds and eventually the related Cs) where C.value
> has the maximum value, given the same D.
>
> So for example, given a table D made of
> [id, otherfield]
> (1, "A")
> (2, "B")
> (3, "C")
>
> and a table C made of
> [id, d_id, value]
> (1, 1, 0)
> (2, 1, 1) <- max value for d_id 1
> (3, 2, 3) <- max value for d_id 2
> (4, 2, 2)
>
> I'd like my query to return
> [d.id, c.id]
> (1, 2)
> (2, 3)
> (3, None)
>
> Is this possible with a single query?
> Many thanks for your time!
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
