On Mon, Oct 26, 2009 at 10:09 PM, Bobby Impollonia <[email protected]> wrote:
> > You can also create a view mapped to that union and use that as a > virtual table so that you don't have to repeat the union specification > for every query: > http://www.w3schools.com/Sql/sql_view.asp > > I don't know if that helps for SQLAlchemy though. > > On Mon, Oct 26, 2009 at 5:59 PM, AF <[email protected]> wrote: > > > > > > > > On Oct 26, 8:48 pm, AF <[email protected]> wrote: > >> Hello, > >> > >> I don't know if this is even possible is SQL, so please bear with > >> me.... :) > >> > >> There are a couple a tables (say, a & b) that are used as "logs" for > >> two different processes. They both have the same simple structure. > >> (id, time_stamp, user_id, message) > >> > >> I would like to create a query that merges the data and returns > >> following results: > >> > >> "time_stamp, user_id, a_or_b, message" > >> > >> (where a_or_b is a value that indicates which table the data row came > >> from) > >> > >> Can this be done in SQL/SQLAlchemy. > >> > >> Thank you, > >> :) > >> > >> p.s. > >> > >> Alternatively, the message columns do not need to be merged.... though > >> I guess time_stamp / user would still need to be. > >> > >> That is: "time_stamp, user_id, message_a, message_b" > >> > >> I don't know if that makes any easier... > > > > > > OK: > > http://www.w3schools.com/Sql/sql_union.asp > > > > Doh. OK, so now I have an SQL statement I wrote by hand that works > > fine, but I still have two questions: > > > > 1) Can this be done via the SQA ORM? > > 2) If not, how should I I build this using non-ORM SQA? > > > > Thank you, > > :) > > > > > > Let's say you have 2 mapped classes class Stuff1(Base): --- etc. ---- class Stuff2(Base) --- etc. ---- You can do something like this q1 = session.query(Stuff1.columnA, Stuff1.columnB, literal_column("'S1'").label('source')) q2 = session.query(Stuff2.columnA, Stuff2.columnB, literal_column("'S2'").label('source')) subq = session.query().from_statement(union_all(q1, q2)).subquery() qry = session.query(subq) Note: just in case your font makes the quotes hard to read, "'S1'" is double-quote,single-quote,S,1,single-quote,double-quote --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
