How to do a union of multiple queryset from a clickhouse ORM

In a Django search app, I want to query a clickhouse database (using the infi.clickhouse_orm library) for pairs of values such as (a=1 AND b>=1.5) OR (a=2 AND b>=1). In SQL this could be done with

select * from table where a == 1 and b >= 1.5 UNION ALL select * from table where a == 2 and b >= 1 

Looking at other exemples I have tried:

With the queryset defined as

qs = TABLE.objects_in(db) qs_1 = qs.filter(A__eq=1, B__gte=1.5) qs_2 = qs.filter(A__eq=2, B__gte=1) 

The | operator

qs_union = qs_1 | qs_2 

which returns

unsupported operand type(s) for |: 'QuerySet' and 'QuerySet' 

The UNION operator

qs_union = qs_1.union(qs_2) 

which returns

'QuerySet' object has no attribute 'union' 

and the Q objects

qs_union = qs.filter(Q(A__eq=1, B__gte=1.5) | Q(A__eq=2, B__gte=1)) 

which returns

'Q' object has no attribute 'to_sql' 

From a clickhouse models, how do you perfom a union of 2, or more, queryset?

Thanks!

1 Answer

Short answer: you should use the Q class of infi.clickhouse_orm.query, like:

from infi.clickhouse_orm.query import Q

The Q-class in info.clickhouse_orm [GitHub] has a to_sql method:

class Q(object): # ... def to_sql(self, model_cls): if self._fovs: sql = ' {} '.format(self._mode).join(fov.to_sql(model_cls) for fov in self._fovs) else: if self._l_child and self._r_child: sql = '({} {} {})'.format( self._l_child.to_sql(model_cls), self._mode, self._r_child.to_sql(model_cls)) else: return '1' if self._negate: sql = 'NOT (%s)' % sql return sql

Since the errors says it can not find the to_sql, it looks like you did not use that Q-class, but Django's Q class.