That's it, awesome! Thank you!
>>> q2 = s.query(q1).order_by(q1.c.lastname, q1.c.firstname)
>> 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.
Thanks again for all your help!
On Sep 19, 2013, at 12:33 PM, Michael Bayer <[email protected]> wrote:
> 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 <[email protected]> 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 <[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.
