here's a proof of concept which completes in postgresql for me: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Appl(Base):
__tablename__ = 'appl'
id = Column(Integer, primary_key=True)
firstname = Column(String)
lastname = Column(String)
refid = Column(Integer)
appldate = Column(DateTime)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)
s = Session(e)
q1 = s.query(distinct(Appl.refid), Appl).\
filter(Appl.lastname.ilike('Williamson%')).\
filter(Appl.firstname.ilike('d%')).\
group_by(Appl).\
order_by(Appl.refid, Appl.appldate.desc())
q1 = q1.cte('distinct_query')
q2 = s.query(q1).order_by(q1.c.lastname, q1.c.firstname)
q2.all()
query outputs as:
WITH distinct_query AS
(SELECT DISTINCT appl.refid AS anon_1, appl.id AS id, appl.firstname AS
firstname, appl.lastname AS lastname, appl.refid AS refid, appl.appldate AS
appldate
FROM appl
WHERE appl.lastname ILIKE %(lastname_1)s AND appl.firstname ILIKE
%(firstname_1)s GROUP BY appl.id, appl.firstname, appl.lastname, appl.refid,
appl.appldate ORDER BY appl.refid, appl.appldate DESC)
SELECT distinct_query.anon_1 AS distinct_query_anon_1, distinct_query.id AS
distinct_query_id, distinct_query.firstname AS distinct_query_firstname,
distinct_query.lastname AS distinct_query_lastname, distinct_query.refid AS
distinct_query_refid, distinct_query.appldate AS distinct_query_appldate
FROM distinct_query ORDER BY distinct_query.lastname, distinct_query.firstname
turning it into aliased(), while unnecessary, works also:
q1 = q1.cte('distinct_query')
q1 = aliased(q1, 'd_q_a')
q2 = s.query(q1).order_by(q1.c.lastname, q1.c.firstname)
q2.all()
On Sep 17, 2013, at 6:39 PM, Nathan Mailg <nathanma...@gmail.com> wrote:
> I'm using SA 0.8.2 and trying to port this query that works with PostgreSQL
> 9.2.4:
>
> WITH distinct_query AS (
> SELECT DISTINCT ON (refid) *
> FROM appl
> WHERE lastname ILIKE 'Williamson%' AND firstname ILIKE 'd%'
> GROUP BY refid, id, lastname, firstname, appldate
> ORDER BY refid, appldate DESC
> )
> SELECT * FROM distinct_query ORDER BY lastname, firstname;
>
> I've worked on this quite a while and I'm stuck. I've tried every construct
> in the docs that looks like it might work without success (subquery, join,
> select, etc). Here's what I've been referencing most recently that I think is
> the closest to what I want:
>
> http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html?highlight=cte#sqlalchemy.orm.query.Query.cte
>
> Here's what I have so far:
>
> qlast, qfirst = params['query'].split(' ', 1)
> subq = DBSession.query(Appl).\
> distinct(Appl.refid).\
> filter(getattr(Appl, 'lastname').match(qlast)).\
> filter(getattr(Appl, 'firstname').ilike(qfirst+'%')).\
> group_by(Appl.refid).\
> group_by(Appl.appldate).\
> order_by(Appl.refid).\
> order_by(Appl.appldate.desc()).\
> cte('distinct_query')
> a = aliased(subq, name='distinct_query_alias')
> rows = DBSession.query(Appl).\
> order_by(a.c.lastname.asc()).\
> order_by(a.c.firstname.asc()).\
> order_by(a.c.middlename.asc())
>
> Below is the error output. Sorry for the length of output; I elided as much
> as possible, but didn't want to remove anything that might be important:
>
> (ProgrammingError) missing FROM-clause entry for table "distinct_query_alias"
> LINE 2: ...race_2 ON race_2.id = applbenef_1.raceid ORDER BY distinct_q...
> ^
> 'SELECT appl.id AS appl_id, appl.refid AS appl_refid,
> appl.appldate AS appl_appldate, appl.lastname AS appl_lastname,
> appl.firstname AS appl_firstname, appl.middlename AS appl_middlename,
> appl.cityid AS appl_cityid, appl.cityid2 AS appl_cityid2,
> appl.raceid AS appl_raceid, appl.maritalid AS appl_maritalid,
> appl.referral AS appl_referral,
> city_1.id AS city_1_id, city_1.name AS city_1_name,
> [... city_1.xxx AS city_1_xxx ...]
> city_2.id AS city_2_id, city_2.name AS city_2_name,
> [... city_2.xxx AS city_2_xxx ...],
> race_1.id AS race_1_id, race_1.name AS race_1_name,
> race_1.race AS race_1_race, race_1.rowcreated AS race_1_rowcreated,
> race_1.rowmodified AS race_1_rowmodified,
> marital_1.id AS marital_1_id, marital_1.name AS marital_1_name,
> marital_1.marital AS marital_1_marital,
> marital_1.rowcreated AS marital_1_rowcreated,
> marital_1.rowmodified AS marital_1_rowmodified,
> agency_1.id AS agency_1_id, agency_1.name AS agency_1_name,
> [... agency_1.xxx AS agency_1_xxx ...],
> applreferrer_1.id AS applreferrer_1_id,
> applreferrer_1.applid AS applreferrer_1_applid,
> applreferrer_1.agencyid AS applreferrer_1_agencyid,
> [... applreferrer_1.xxx AS applreferrer_1_xxx ...],
> applsponsor_1.id AS applsponsor_1_id,
> applsponsor_1.applid AS applsponsor_1_applid,
> [... applsponsor_1.xxx AS applsponsor_1_xxx ...],
> race_2.id AS race_2_id, race_2.name AS race_2_name,
> race_2.race AS race_2_race, race_2.rowcreated AS race_2_rowcreated,
> race_2.rowmodified AS race_2_rowmodified,
> applbenef_1.id AS applbenef_1_id,
> applbenef_1.applid AS applbenef_1_applid,
> applbenef_1.lastname AS applbenef_1_lastname,
> applbenef_1.firstname AS applbenef_1_firstname,
> applbenef_1.age AS applbenef_1_age,
> applbenef_1.raceid AS applbenef_1_raceid,
> applbenef_1.notes AS applbenef_1_notes,
> applbenef_1.rowcreated AS applbenef_1_rowcreated,
> applbenef_1.rowmodified AS applbenef_1_rowmodified \n
> FROM appl
> LEFT OUTER JOIN city AS city_1 ON city_1.id = appl.cityid
> LEFT OUTER JOIN city AS city_2 ON city_2.id = appl.cityid2
> LEFT OUTER JOIN race AS race_1 ON race_1.id = appl.raceid
> LEFT OUTER JOIN marital AS marital_1 ON marital_1.id = appl.maritalid
> LEFT OUTER JOIN applreferrer AS applreferrer_1 ON appl.id =
> applreferrer_1.applid
> LEFT OUTER JOIN agency AS agency_1 ON agency_1.id = applreferrer_1.agencyid
> LEFT OUTER JOIN applsponsor AS applsponsor_1 ON appl.id =
> applsponsor_1.applid
> LEFT OUTER JOIN applbenef AS applbenef_1 ON appl.id = applbenef_1.applid
> LEFT OUTER JOIN race AS race_2 ON race_2.id = applbenef_1.raceid
> ORDER BY
> distinct_query_alias.lastname ASC,
> distinct_query_alias.firstname ASC,
> distinct_query_alias.middlename ASC,
> applreferrer_1.id,
> applsponsor_1.id,
> applbenef_1.id' {}
>
> class Appl(Base):
> __tablename__ = 'appl'
> id = Column(Integer, primary_key=True)
> refid = Column(Integer, Sequence('appl_refid_seq'))
> appldate = Column(Date)
> lastname = Column(Unicode(50))
> firstname = Column(Unicode(50))
> middlename = Column(Unicode(50))
> cityid = Column(Integer, ForeignKey('city.id'))
> cityid2 = Column(Integer, ForeignKey('city.id'))
> raceid = Column(Integer, ForeignKey('race.id'))
> maritalid = Column(Integer, ForeignKey('marital.id'))
> #
> city = relationship('City', lazy='joined',
> primaryjoin='City.id==Appl.cityid')
> city2 = relationship('City', lazy='joined',
> primaryjoin='City.id==Appl.cityid2')
> race = relationship('Race', lazy='joined')
> marital = relationship('Marital', lazy='joined')
> applrefs = relationship('ApplReferrer', cascade="all, delete,
> delete-orphan",
> lazy='joined', order_by='ApplReferrer.id')
> applsponsors = relationship('ApplSponsor', backref='appl', cascade="all,
> delete, delete-orphan",
> lazy='joined', order_by='ApplSponsor.id')
> applbenefs = relationship('ApplBenef', cascade="all, delete,
> delete-orphan",
> lazy='joined', order_by='ApplBenef.id')
>
> Please let me know if you need more info. Thanks!
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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
