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.
