On Nov 7, 2013, at 6:46 PM, Daniel Grace <[email protected]> wrote:
> I've been using the ORM exclusively for my project and I've run into a > particular case that has me stumped: > > I'm trying to find a way to generate SQL that essentially looks like this, > using session.query() as a base: (Running on PostgreSQL) > > SELECT model.*, series.number > FROM model, generate_series(1,100) AS series(number) > WHERE some_where_clauses AND series.number some_condition Ok well selecting from a function as a FROM clause is something that you need to set up with a special select() statement (see http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#functions), though that syntax you have there where you’re adding in a “column definition list” ( a term I just read at http://www.postgresql.org/docs/9.0/static/sql-select.html, though what you have there doesn’t seem to look the same) isn’t really straightforward right now. If i understand what that actually means, then you’d get that effect, using an extra subquery, like this: from sqlalchemy import Column, Integer, select, func from sqlalchemy.orm import Session from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.sql import column Base = declarative_base() class Model(Base): __tablename__ = 'model' id = Column(Integer, primary_key=True) series = select([column('number')]).select_from(func.generate_series(1, 100)) sess = Session() q = sess.query(Model, series.c.number).filter(Model.id > series.c.number) print q output: SELECT model.id AS model_id, number AS number FROM model, (SELECT number FROM generate_series(:generate_series_1, :generate_series_2)) WHERE model.id > number that’s basically equivalent. If you want the identical syntax with that function, the @compiles construct would be a start, but you’d be probably making a custom FromClause subclass which is a little involved. > > The closest solutions I've found all have one or more of the following > problems: > They non-ORM methods (e.g. select() instead of query() and a lot of text()) > They end up wrapping generate_series in a subselect -- SELECT .... FROM > (SELECT generate_series(...)). This is technically legal(*) > They don't give me a way to properly alias both the "table" and its columns > (in the case of session.query(...).select_from(sql.func.generate_series(...))) > Any ideas what I should be doing here? FWIW, it seems like this similar > situation could potentially crop up if using a RDBMS that supported > parameterized views (Postgres doesn't, yet) -- though at least that situation > would (probably) know what the output columns are from reflection. > > -- Daniel > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
