On Jan 28, 2008, at 12:03 PM, Stefano Bartaletti wrote:
>
> Hello,
>
> I have two tables defined this way:
>
> tabItems = sqa.Table(meta, "items",
> sqa.Column("id", sqa.Integer, primary_key=True),
> )
> tabTracking = sqa.Table(meta, "tracking",
> sqa.Column("id", sqa.Integer, primary_key=True),
> sqa.Column("item_id", sqa.Integer, sqa.ForeignKey("items.id")),
> sqa.Column("date_start", sqa.DateTime, default="now"),
> sqa.Column("date_end", sqa.DateTime, default="infinity"),
> )
>
> Now I would like the "Item" mapper to automagically get the last
> Tracking
> record (through MAX() on date_start field)
>
> In SQL should be:
>
> select items.*, tracking.*
> from items
> left join tracking on
> tracking.item_id=items.id
> and tracking.date_start = (
> select max(date_start)
> from tracking t1
> where t1.item_id=tracking.item_id
> )
>
> Is it possible to write a relation() to perform such a task at
> mapper level?
sure , just use a custom primaryjoin condition. We have some trac
tickets related to improving the behavior of the correlation you're
doing there (correlating tracking to the parent items table in the
subquery) in conjunction with a relation(), however, if you use
lazy=False it is doable right now, as in (replace "users" with
"items", "stuff" with "tracking"):
salias = stuff.alias()
stuff_view =
select
([func
.max
(salias.c.date).label('max_date')]).where(salias.c.user_id==users.c.id)
mapper(User, users, properties={
'stuff':relation(Stuff, lazy=False,
primaryjoin=and_(users.c.id==stuff.c.user_id,
stuff.c.date==(stuff_view.label('foo'))))
})
this example is the "working" version of what's in ticket #948. a few
other combinations of the above are not yet working, namely if you
tried using lazy=True, or if you put an explicit correlate(users) on
the stuff_view selectable.
theres other ways to do this too, like not using the correlation to
"items" and instead joining to a view of all the max(date_start)s,
though that tends to produce less efficient queries (but also work
better with SQLAlchemy at the moment).
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
