[PonyORM-list] A simple query going wrong

[Matthew Bell]

Hi,

The problematic query I have is:

In [9]: count(k for k in Keyword if count(k.users) > 0 and k.scheduled <
datetime.date.today())
SELECT COUNT(*)
FROM "keyword" "k"
  LEFT JOIN "keyword_user" "t-1"
    ON "k"."id" = "t-1"."keyword"
WHERE "k"."scheduled" < %(p1)s
GROUP BY "k"."id"
HAVING COUNT(DISTINCT "t-1"."user") > 0
{'p1':datetime.date(2015, 5, 19)}

It is always only returning one result, I think because of the distinct.

On the other hand, this query works fine:

In [3]: count(k for k in Keyword if count(u for u in k.users) > 0 and
k.scheduled < datetime.date.today())
GET CONNECTION FROM THE LOCAL POOL
SWITCH TO AUTOCOMMIT MODE
SELECT COUNT(*)
FROM "keyword" "k"
WHERE (
    SELECT COUNT(DISTINCT "t-1"."user")
    FROM "keyword_user" "t-1"
    WHERE "k"."id" = "t-1"."keyword"
    ) > 0
  AND "k"."scheduled" < %(p1)s
{'p1':datetime.date(2015, 5, 19)}

Why do these generate different SQL?

Thanks

-- 
Regards,

Matthew Bell
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </ponyorm-list/attachments/20150519/aca963fb/attachment.html>