AF wrote: > Hello, > > [Note: This is a revised edition of my previous post "multi table > select"] > > I have events logged to several diffeerent tables, and I need to get > the union of these different tables. (The event types use different > tables for various reasons.) > > Here is what I am doing now, and it seems to work: > > # Note the static 'event_type' columns > stmt = 'SELECT * FROM (SELECT "A" AS event_type, user_id as user, > notes, time_created ' > stmt = stmt + 'FROM events_a WHERE user_id = :uid ' > stmt = stmt + 'UNION ALL ' > stmt = stmt + 'SELECT SELECT "B" AS event_type, user_id as user, > notes, time_created ' > stmt = stmt + 'FROM events_b WHERE user_id = :uid) as events_all ' > stmt = stmt + 'ORDER BY events_all.time_created DESC' > > res = DBSession.execute(stmt, dict(uid = uid)) > records = res.fetchall() > > So I have several questions: > > 1) Is the the raw SQL I am using sane? >
There are a few things that would make it more palatable to SQLAlchemy: 1. Move the "WHERE user_id = :uid" clauses to the outer SELECT statement. This will make it easier to use in more complicated queries. I don't think this will kill your performance, but hey what do I know? 2. Use explicit columns in the outer SELECT instead of the wildcard. SQLAlchemy will do this for you if you use its SQL expression functions, so don't worry about this. > 2) How can I use SQLAlchemy to simply things? > > 3) How would I add tables events_c? events_d? > This thread describes a very similar situation: http://groups.google.com/group/sqlalchemy/browse_thread/thread/21f47b750b672ea9 Using that as a template, you can do this (assuming event_a, event_b, etc. are Table objects): events_selectable = union_all( event_a.select().column(literal_column("'A'").label("event_type") event_b.select().column(literal_column("'B'").label("event_type") event_c.select().column(literal_column("'C'").label("event_type") event_d.select().column(literal_column("'D'").label("event_type")) # At this point, you can map class Event to events_selectable (not shown). # Now, query events for a given uid: q = DBSession.query(Event) q = q.filter_by(user_id=uid) q = q.order_by(Event.time_created.desc()) records = q.all() # or, without using the ORM: record_rows = DBSession.execute(events_selectable.select( events_selectable.c.user_id == uid, order_by=events_selectable.c.time_created.desc()).fetchall() > 4) Since the events_x tables are already defined with > declarative_base, is there a reasonable way to make an SQLAlchemy > "Events" object that knows where to retrieve and insert event records > based on the "event_type" field? > For your previous posts, I thought you wanted a single class (Event) mapped to the union, which is what the ORM example above assumes. It will handle retrieving event records just fine (e.g. filter_by(event_type="A")), but will not work for inserting them. To do that, you will need separate EventA, EventB, etc. classes. This will probably clash with the union class in ways that I don't know how to resolve, so watch out. However, you can still call insert() directly on the table objects, e.g. event_a.insert().values(user_id=uid, ...). You just don't get the nice ORM interface. -Conor --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
