OK, like this then:
q1 = s.query(Appl).distinct(Appl.refid).\
filter(Appl.lastname.ilike('Williamson%')).\
filter(Appl.firstname.ilike('d%')).\
group_by(Appl).\
order_by(Appl.refid, Appl.appldate.desc())output: WITH distinct_query AS (SELECT DISTINCT ON (appl.refid) 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.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 I know this is not exactly the same, but the query itself is a SELECT DISTINCT ON(x), which seems to be what they're getting at when I look at http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT. On Sep 18, 2013, at 5:39 PM, Nathan Mailg <nathanma...@gmail.com> wrote: > Thanks Mike for all the help! I think we're really close. > > Unfortunately, the "DISTINCT ON (refid) *" syntax appears to be needed, as > opposed to "DISTINCT (refid), *", since the following query in psql is not > filtering the duplicate refid's: > > WITH distinct_query AS (SELECT DISTINCT (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; > > and the above is what appears to be the equivalent of: > >> 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) > > > I got the above code running with no exceptions, so that's great, but it's > not getting rid of rows with the same refid in "distinct_query". Is there a > way I can get "DISTINCT ON (refid) *" generated? > > Sorry to tag on a followup, but how would I get the following relationship > mapped onto the 2nd, non-distinct SELECT ("q2" above)? From the "Appl" class > below: > >>> city = relationship('City', lazy='joined', >>> primaryjoin='City.id==Appl.cityid') > > > I tried some aliased join variations but couldn't get anything to work. I > think there's some additional configuration needed as I have two columns > (cityid and cityid2) in table Appl that reference the City table. > > I really appreciate all your help! > > > On Sep 17, 2013, at 10:51 PM, Michael Bayer <mike...@zzzcomputing.com> wrote: > >> here's a proof of concept which completes in postgresql for me: >> >> […] >> >> 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 >>> >>> […] >>> >>> 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
