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
-~----------~----~----~----~------~----~------~--~---
