Sorry for this follow-up, but I'm really at a loss as to what to check next…
What should I do if after I insert a new "Appl" row, the query we've worked on
in this thread is *not* finding it, i.e. it's not being returned in the result
set? I'm really surprised and thinking I must be missing something really basic?
I've deleted the lastname, firstname indexes and re-created them, but the newly
created row is still *not* being returned!
Here's the sql insert from the SA log:
2013-10-03 12:26:37,638 INFO [sqlalchemy.engine.base.Engine][Dummy-3] BEGIN
(implicit)
2013-10-03 12:26:37,643 INFO [sqlalchemy.engine.base.Engine][Dummy-3] INSERT
INTO appl (refid, appldate, lastname, firstname, cityid, cityid2) VALUES
(nextval('appl_refid_seq'), %(appldate)s, %(lastname)s, %(firstname)s,
%(cityid)s, %(cityid2)s) RETURNING appl.id
2013-10-03 12:26:37,643 INFO [sqlalchemy.engine.base.Engine][Dummy-3]
{'cityid2': None, 'cityid': None, 'firstname': u'test3', 'lastname': u'test3',
'appldate': u'10/3/2013'}
2013-10-03 12:26:37,644 DEBUG [sqlalchemy.engine.base.Engine][Dummy-3] Col
('id',)
2013-10-03 12:26:37,644 DEBUG [sqlalchemy.engine.base.Engine][Dummy-3] Row
(14574,)
2013-10-03 12:26:37,645 INFO [sqlalchemy.engine.base.Engine][Dummy-3] COMMIT
What's also really odd, is if I manually run the sql in psql, it works and
returns the new row:
WITH distinct_query AS (
SELECT DISTINCT ON (refid) id, refid, lastname, firstname, appldate
FROM appl WHERE lastname ILIKE 'test%'
GROUP BY refid, id, lastname, firstname, appldate ORDER BY refid, appldate
DESC
)
SELECT * from distinct_query ORDER BY lastname, firstname;
Another odd thing is in my application, I have another search feature, and it's
using an SA query that's not using the cte/distinct query below, and it works!
Sorry to have to ask this, but I'm stuck and need to get this working.
Thanks again for all the help!
On Oct 1, 2013, at 1:22 PM, Nathan Mailg <nathanma...@gmail.com> wrote:
> Thanks Mike, that works!
>
> I really appreciate you taking the time to show me how to get this working.
>
> Do you have an amazon wish list or something like that somewhere?
>
>
> On Sep 30, 2013, at 2:09 PM, Michael Bayer <mike...@zzzcomputing.com> wrote:
>
>> qlast, qfirst = 'a', 'b'
>> d = DBSession.query(Appl).\
>> distinct(Appl.refid).\
>> filter(Appl.lastname == qlast).\
>> filter(Appl.firstname == qfirst).\
>> group_by(Appl).\
>> order_by(Appl.refid)
>> d = d.cte('distinct_query')
>>
>> q = DBSession.query(Appl).select_entity_from(d).\
>> join(Appl.city).\
>> order_by(d.c.lastname, d.c.firstname)
>>
>> for row in q:
>> print row.refid, row.firstname, row.lastname, row.city.name
>
--
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.
