Peewee: using count(*) in a select query

Question

Assume that we have a hypothetical table that looks something like this:

id          color          group_id
----------  -------------  ----------
1           red            100
2           blue           101
3           orange         100
4           red            102
5           pink           103
6           red            104
7           orange         104
8           orange         105

I want to select the group ID which contains all the colors of a particular set of colors. Let's say I want to search for the group ID where the colors are red and orange. The raw SQL query would be something like:

  SELECT group_id 
    FROM colors
   WHERE color
      IN ('red', 'orange')
GROUP BY group_id
  HAVING COUNT(*) = 2;

This would return group IDs 100 and 104. What is the Peewee SelectQuery for this? I'm having trouble finding out how to represent the COUNT(*) bit.

Answer 1

Sure thing:

(Colors
 .select(Colors.group)
 .where(Colors.color << ('red', 'orange'))
 .group_by(Colors.group)
 .having(fn.COUNT(Colors.id) == 2))

Alternatively, you could do:

.having(fn.COUNT(SQL('*')) == 2)

There's some overlap here with the "top N objects per group" type of situation. A number of solutions are documented here:

http://docs.peewee-orm.com/en/latest/peewee/hacks.html#top-n-objects-per-group

Finally, this is also similar to finding objects tagged with a particular set of tags. There are example queries on my blog, here:

http://charlesleifer.com/blog/a-tour-of-tagging-schemas-many-to-many-bitmaps-and-more/