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.
On 2/12/07, Mike Orr <[EMAIL PROTECTED]> wrote:
>
> I have a MySQL table with three date fields. I need to use the
> coalesced value (i.e., the first one that's not NULL) in WHERE,
> HAVING, ORDER BY, and I'd like it to also appear as an attribute in my
> session-mapped class. I can see how to do parts of this, but not how
> to put it all together.
>
> My current task is to get the ten most recent records and display
> their title and date in a Pylons application.
>
> ====
> class Incident(object):
> pass
>
> assign_mapper(ctx, Incident, incident_table)
>
> activity = sa.func.coalesce(
> Incident.c.last_entry_date,
> Incident.c.activity_date,
> Incident.c.create_date,
> )
>
> recent = sa.select(
> [Incident.c.id, Incident.c.title, activity.label("activity")],
> Incident.c.is_public,
> order_by=[sa.desc(activity)], limit=10).execute().fetchall()
> ====
>
> This works but I'd rather get the result fields as attributes rather
> than keys. I could wrap the results in a custom class but I may as
> well use the Incident class that's sitting there.
>
> ====
> class Incident(object):
> @property
> def activity(self):
> return self.last_entry_date or self.activity_date or
> self.create_date
> ====
>
> I haven't tried this yet but I'd be specifying the coalescence twice
> in different ways, which is not ideal. Is this the best way to go?
>
> I could just use the property and do the sorting/eliminating in
> Python, but then I'd be pulling in all these database records I'll
> never select.
>
> I guess what I'd really like is a calculated column in the table
> object, which I could use in all the clauses and also propagate to
> Incident. But I'd have to append it to an autoloaded table object,
> because my table has some thirty columns and is maintained by another
> application, so I don't want to keep updating its schema as the
> underlying table changes.
>
> --Mike
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
