On 2/12/07, Jonathan Ellis <[EMAIL PROTECTED]> wrote:
>
> Instead of mapping your table directly, map a select containing the coalesce:
>
> incidents_with_activity = select([incidents,
> func.coalesce(...).label('activity')]).alias('incidents_with_activity')
> assign_mapper(Incident, incidents_with_activity)
>
> then you can use the activity label anywhere in the ORM queries.
Thanks, that works perfect. I just had to give my select an alias to
avoid a mapper exception, and cast the result to a date because it was
defaulting to a string. For the record, here's my code now.
===MODEL===
import pylons.database
import sqlalchemy as sa
from sqlalchemy.ext.assignmapper import assign_mapper
ctx = pylons.database.session_context
engine = ctx.current.bind_to
meta = sa.BoundMetaData(engine)
incident_table = sa.Table("Incident", meta, autoload=True)
entry_table = sa.Table("Entry", meta, autoload=True)
inews_date_column = sa.func.date(
sa.func.coalesce(
incident_table.c.last_entry_date,
incident_table.c.activity_date,
incident_table.c.create_date,
)).label("inews_date")
incident_select = sa.select([
incident_table,
inews_date_column,
]).alias("incident_select")
class Incident(object):
pass
class Entry(object):
pass
assign_mapper(ctx, Incident, incident_select)
assign_mapper(ctx, Entry, entry_table)
===DATA RETRIEVAL===
cols = Incident.c
query = ctx.current.query(Incident)
recent = query.select(cols.is_public,
order_by=[sa.desc(cols.inews_date)], limit=recent_limit)
--
Mike Orr <[EMAIL PROTECTED]>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
