Issue: SelectQuery.count() problem
Author: awahlig
State: closed
Created: 2011-12-16 01:39:51

I was doing a query using annotate() (with the default Count aggregation) to order by the count of related models.

The query itself worked well but calling query.count() failed with:

```
OperationalError: no such column: count
```

Further investigation revealed that SelectQuery.count() replaces the query with bare COUNT(x) but doesn't remove the ORDER BY clause which in my case created an invalid statement referencing an unknown column alias.

Here's how I fixed it:

``` python

    def count(self):
        tmp = self.query, self._limit, self._offset, self._order_by
        self._limit = self._offset = None
        self._order_by = []
        try:

            if self.use_aliases():
                self.query = 'COUNT(t1.%s)' % (self.model._meta.pk_name)
            else:
                self.query = 'COUNT(%s)' % (self.model._meta.pk_name)

            res = self.database.execute(*self.sql())

        finally:
            # restore
            self.query, self._limit, self._offset, self._order_by = tmp

        return (res.fetchone() or [0])[0]
```

Two other things that I changed are:
- check whether fetchone() returned None (which is the case if the table is empty) and return 0 in that case
- use try..finally block to make sure the state is restored in case of an error
- replace multiple tmp variables with one


Hmm... are you using the latest version of peewee?  I believe the ordering thing was fixed in ca8945a7 .  I'll add your logic to handle 0 correctly: 36d5314c0f1c6b5cd01d5b15420d27f21be099e2

The try/finally isn't needed since the fix in the first commit referenced does a clone()


You're right. I was using an older version. Sorry for that.

Anyway, I've noticed that count() returns weird results for queries with joins. In the current implementation of count() such queries will return one count for each joined row. Right now count() will return the first count but what we really want is the number of them.

I spent some time on this and I came up with this:

``` python
    def count(self):
        clone = self.order_by()
        clone._limit = clone._offset = None

        sql, params = clone.sql()
        res = clone.database.execute('SELECT COUNT(1) FROM (%s)' % sql, params)

        return res.fetchone()[0]
```

It's a subquery that contains the original query without ordering and pagination.

Earlier version of this post also changed the "clone.query" to "1". Later I found out that this breaks count() for "DISTINCT" queries. Any change to the "query" attribute of distinct queries could alter their row count. I've edited out this part because it was just an optimization anyway.

Also, it was actually a join query with no results that caused fetchone() to return None. With this subquery this is not possible anymore. The resulting outer query is always simple and always returns one result -- the count -- so there's no need for the "or [0]" logic.


Was able to replicate this and have added a testcase that is currently failing in both sqlite and psycopg2.  Working on fixing using your suggestedi mplementation but am dealing w/numerous test failures.


Fixed in 63bdeba57dd198ba243d42aeb74d93afc0ff6001 -- basically just wrap the query if its distinct. Thanks for your help on this one!!


Hmm, there's a case where the current code still doesn't work correctly :)

Consider this:

``` python
from peewee import *

class Blog(Model):
    dummy = IntegerField()

class Entry(Model):
    blog = ForeignKeyField(Blog)

Blog.create_table()
Entry.create_table()

blog = Blog()
blog.save()

for i in xrange(10):
    Entry(blog=blog).save()

query = Blog.select().annotate(Entry)
print 'count', query.count()
print 'wrapped-count', query.wrapped_count()
print 'len', len(list(query))
```

Output:

```
count 10
wrapped-count 1
len 1
```

The query issued by count() was:

```
SELECT COUNT(t1.id) FROM blog AS t1
    INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id, t1.dummy
```

which returns the number of entries in each blog, not the number of blogs.

The solution that works for me is to use wrapped_count() for all queries with "GROUP BY":

``` python
    def count(self):
        if self._distinct or self._group_by:
            return self.wrapped_count()
```

By the way, I really appreciate your work. I used your whole toolset (peewee, flask_peewee, wtfpeewee) for my project and it works great. High quality and well documented code. Keep it up!