Hi, thanks for your help! That works.
.oO V Oo.
On 09/19/2011 11:03 PM, Michael Bayer wrote:
On Sep 19, 2011, at 12:45 PM, Vlad K. wrote:
Hi!
I have a model, let's call it Resource. And another, let's call it Container. Each
container can have any number of Resources, so they're in many-to-many relationship.
What's worse, they're in "association object pattern" relationship because the
association also carries the amount of particular Resource associated with teh Container.
Now, when a Container is viewed I need to construct a (html) table listing ALL
resources available (simple select all from resources), but ordered so that those
resources that are in the container (amount> 0) are at the top (ordered desc),
followed by the resources that are not in the container (implicitly amount = 0).
this is an ORDER BY derived from join. SQL would be like:
select * from resource left outer join container_to_resource on
resource.id=container_to_resource.resource_id and
container_to_resource.container_id=<my container id> order by
coalesce(container_to_resource.count, 0)
ORM:
Session.query(Resource).\
outerjoin(ResourcesInContainers.resource).\
filter(ResourcesInContainers.container_id=mycontainer.id).\
order_by(func.coalesce(ResourcesInContainers.amount, 0))
I'd stay away from UNION as they are awkward and rarely needed - only if you
have two disjoint selectables that really need to be in the same result.
--
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.
