I don't know why, but removing the "join(Appl.city)" call in "q" fixed it.
Below is the SA output of just the join section, as the full, generated query
output is very long:
Not working, returning only 1 row of 4 expected rows:
q = DBSession.query(Appl).\
select_entity_from(d).\
join(Appl.city).\
order_by(d.c.lastname, d.c.firstname)
FROM distinct_query JOIN city ON city.id = distinct_query.cityid LEFT OUTER
JOIN city AS city_1 ON city_1.id = distinct_query.cityid LEFT OUTER JOIN city
AS city_2 ON city_2.id = distinct_query.cityid2 LEFT OUTER JOIN race AS race_1
ON race_1.id = distinct_query.raceid LEFT OUTER JOIN marital AS marital_1 ON
marital_1.id = distinct_query.maritalid LEFT OUTER JOIN applreferrer AS
applreferrer_1 ON distinct_query.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 distinct_query.id = applsponsor_1.applid LEFT
OUTER JOIN applbenef AS applbenef_1 ON distinct_query.id = applbenef_1.applid
LEFT OUTER JOIN race AS race_2 ON race_2.id = applbenef_1.raceid ORDER BY
distinct_query.lastname, distinct_query.firstname, distinct_query.middlename,
distinct_query.maidenname, applreferrer_1.id, applsponsor_1.id, applbenef_1.id
Working, returning 4 of 4 expected rows:
q = DBSession.query(Appl).\
select_entity_from(d).\
order_by(d.c.lastname, d.c.firstname)
FROM distinct_query LEFT OUTER JOIN city AS city_1 ON city_1.id =
distinct_query.cityid LEFT OUTER JOIN city AS city_2 ON city_2.id =
distinct_query.cityid2 LEFT OUTER JOIN race AS race_1 ON race_1.id =
distinct_query.raceid LEFT OUTER JOIN marital AS marital_1 ON marital_1.id =
distinct_query.maritalid LEFT OUTER JOIN applreferrer AS applreferrer_1 ON
distinct_query.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 distinct_query.id = applsponsor_1.applid LEFT OUTER JOIN
applbenef AS applbenef_1 ON distinct_query.id = applbenef_1.applid LEFT OUTER
JOIN race AS race_2 ON race_2.id = applbenef_1.raceid ORDER BY
distinct_query.lastname, distinct_query.firstname, distinct_query.middlename,
distinct_query.maidenname, applreferrer_1.id, applsponsor_1.id, applbenef_1.id
Thanks Mike!
On Oct 3, 2013, at 4:59 PM, Michael Bayer <[email protected]> wrote:
> are you using serializable isolation ? that would prevent an in-progress
> transaction from seeing this new row committed elsewhere.
>
> other than that, the SELECT in the log will show you what rows it's selecting
> and using echo='debug' will show the rows it finds on the way back.
>
>
> On Oct 3, 2013, at 2:35 PM, Nathan Mailg <[email protected]> wrote:
>
>> 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 <[email protected]> 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 <[email protected]> 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 [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.
