Peewee group_concat/case issue

Question

I'm trying to use peewee to fetch and format some data coming from a sqlite database using GROUP_CONCAT and Case. But I'm facing an issue with those functions.

---

First let start with what I want to achieve:

I simplified my table structure to better point the problem: 1 simple table with two columns: name (Char), is_controlled (Boolean). This SQL request compute the desired result:

SELECT 
    SUM(is_controlled),
    GROUP_CONCAT(CASE WHEN is_controlled = 1 THEN name ELSE NULL END, ':') as controlled,
    GROUP_CONCAT(CASE WHEN is_controlled = 0 THEN name ELSE NULL END, ':') as not_controlled
FROM component;

Output (which is what I expect to have with peewee):

2
comp1:comp3
comp2

---

Here is an script allowing to test my problem:

from peewee import *

db = SqliteDatabase('test.db')


class Component(Model):
name = CharField()
is_controlled = BooleanField()

class Meta:
database = db


raw_data = [
    {'name': 'comp1', 'is_controlled': True},
    {'name': 'comp2', 'is_controlled': False},
    {'name': 'comp3', 'is_controlled': True},
]

db.connect()

# Populate database
db.create_tables([Component])
for item in raw_data:
    Component.get_or_create(**item)

res = Component.select(
    fn.Sum(Component.is_controlled).alias('controlled_count'),
    fn.GROUP_CONCAT(Case(None, [((Component.is_controlled == True), Component.name)], None), ':').alias('controlled'),
    fn.GROUP_CONCAT(Case(None, [((Component.is_controlled == False), Component.name)], None), ':').alias('not_controlled')
)


print res[0].controlled_count
print res[0].controlled
print res[0].not_controlled

db.close()

As you can see, the data structure is simple (I simplified at maximum the example). The ouput is:

2
:comp3:
:

I inspected the SQL query generated by peewee (using res.sql()) and it looks like that:

sql = 'SELECT Sum("t1"."is_controlled") AS "controlled_count", GROUP_CONCAT(CASE WHEN ("t1"."is_controlled" = ?) THEN ? END, "t1"."name") AS "controlled", GROUP_CONCAT(CASE WHEN ("t1"."is_controlled" = ?) THEN ? END, "t1"."name") AS "not_controlled" FROM "component" AS "t1"'

params = [True, ':', False, ':']

We can see that the ELSE NULL part is missing from the peewee generated SQL request. I have tried several things, like adapting the parameters given to the Case function, but I can not get it to work correctly.

How can correctly use peewee to have the same result as using SQL ?

(I'm using python 2.7.15 with peewee 3.6.4 ans sqlite 3.19.4)

Answer 1

The Case function's signature provides a clue:

def Case(predicate, expression_tuples, default=None):

Inside the code, it checks:

if default is not None:
    clauses.extend((SQL('ELSE'), default))

So, when you're passing None it's indistinguishable from the "empty/unspecified" case, and Peewee ignores it.

As a workaround you could instead specify SQL('NULL') as the default value. Or you could use an empty string, although I'm not sure whether you're relying on some behavior of group-concat with nulls, so that may not work?