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 <[email protected]> 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 <[email protected]> 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 [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.
