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.
>
> I figure I can do that the long way, selecting all the resources in a
> sequence, then for each apply an implicit attribute of amount obtained from
> another query, that of resources in this Container.
>
> But I assume there's the "right way" using UNION, (union of resources in the
> container with explicit amount, and all resources with implicit amount=0),
> and I am not sure how to proceed.
>
>
> class Container(Base):
> __tablename__ = "containers"
>
> container_id = ... # Integer
> name = ...
>
> resources = relationship("ResourcesInContainers")
>
>
> class Resource(Base):
> __tablename__ = ...
>
> resource_id = .... # Integer
> name ...
>
>
> class ResourcesInContainers(Base):
> __tablename__ = ...
>
> resource_id = Column(Integer, ForeignKey("resources.resource_id"),
> primary_key=True)
> container_id = Column(Integer, ForeignKey("containers.container_id",
> primary_key=True)
> amount = Column(SmallInteger)
>
> resource = relationship("Resource")
>
>
> I need to query resources' id, name and amount.
>
>
> Thanks!
>
>
> --
>
> .oO V Oo.
>
> --
> 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
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
