engine: propagate filters to dimension's members enumeration (#38067)

bijoe/engine.py

@@ -129,19 +129,21 @@ class EngineDimension(object):
     def __getattr__(self, name):
         return getattr(self.dimension, name)
 
-    @property
-    def cache_key(self):
-        return hashlib.md5(self.engine.path + self.engine_cube.name + self.name).hexdigest()
+    def cache_key(self, filters):
+        return hashlib.md5(self.engine.path + self.engine_cube.name + self.name + repr(filters)).hexdigest()
 
-    @property
-    def members(self):
+    def members(self, filters=()):
         assert self.type != 'date'
 
         if self.type == 'bool':
             return [Member(id=True, label=_('Yes')), Member(id=False, label=_('No'))]
 
-        members = cache.get(self.cache_key)
+        cache_key = self.cache_key(filters)
+        members = cache.get(cache_key)
         if members is not None:
+            self.engine.log.debug('MEMBERS: (from cache) dimension %s.%s filters=%s: %s',
+                                  self.engine_cube.name, self.name, filters,
+                                  members)
             return members
 
         members = []
@@ -150,13 +152,28 @@ class EngineDimension(object):
         value_label = self.value_label or value
         order_by = self.order_by
 
+        joins = set(self.join or [])
+        conditions = []
+        for dimension_name, values in filters:
+            dimension = self.engine_cube.dimensions[dimension_name]
+            if not (set(dimension.join or []) & set(self.join or [])):
+                continue
+            # assert dimension.filter
+            condition, values = dimension.build_filter(values)
+            with self.engine.get_cursor() as cursor:  # Ugly...
+                condition = cursor.mogrify(condition, values)
+            if dimension.filter_needs_join and dimension.join:
+                joins.update(dimension.join)
+            conditions.append(condition)
+            joins.update(dimension.join)
+
         with self.engine.get_cursor() as cursor:
             sql = self.members_query
             if not sql:
                 table_expression = '%s' % self.engine_cube.fact_table
-                if self.join:
+                if joins:
                     table_expression = self.engine_cube.build_table_expression(
-                        self.join, self.engine_cube.fact_table)
+                        joins, self.engine_cube.fact_table)
                 sql = 'SELECT %s AS value, %s::text AS label ' % (value, value_label)
                 sql += 'FROM %s ' % table_expression
                 if order_by:
@@ -170,6 +187,8 @@ class EngineDimension(object):
                 for order_value in order_by:
                     if order_value not in group_by:
                         group_by.append(order_value)
+                if conditions:
+                    sql += 'WHERE %s ' % (' AND '.join(conditions))
                 sql += 'GROUP BY %s ' % ', '.join(group_by)
                 sql += 'ORDER BY (%s) ' % ', '.join(order_by)
             sql = sql.format(fact_table=self.engine_cube.fact_table)
@@ -179,7 +198,10 @@ class EngineDimension(object):
                 if row[0] is None:
                     continue
                 members.append(Member(id=row[0], label=unicode(row[1])))
-        cache.set(self.cache_key, members, 600)
+        cache.set(cache_key, members, 600)
+        self.engine.log.debug('MEMBERS: dimension %s.%s filters=%s: %s',
+                              self.engine_cube.name, self.name, filters,
+                              members)
         return members
 
 
@@ -217,10 +239,9 @@ class EngineJSONDimension(EngineDimension):
         self.engine_cube = engine_cube
         self.dimension = SchemaJSONDimension(self.engine_cube.json_field, name)
 
-    @property
-    def cache_key(self):
+    def cache_key(self, filters):
         return hashlib.md5(self.engine.path + self.engine_cube.json_field
-                           + self.engine_cube.name + self.name).hexdigest()
+                           + self.engine_cube.name + self.name + repr(filters)).hexdigest()
 
     def to_json(self):
         return {

bijoe/visualization/forms.py

@@ -186,7 +186,7 @@ class CubeForm(forms.Form):
                     label=dimension.label.capitalize(), required=False)
             else:
                 members = []
-                for _id, label in dimension.members:
+                for _id, label in dimension.members():
                     members.append((_id, six.text_type(_id), label))
                 members.append((None, '__none__', _('None')))
 

bijoe/visualization/utils.py

@@ -154,7 +154,8 @@ class Visualization(object):
         rows = list(self.cube.query(self.filters.items(),
                                     self.drilldown,
                                     [self.measure]))
-        self.members = {dimension: list(dimension.members) for dimension in self.drilldown}
+        self.members = {dimension: list(dimension.members(filters=self.filters.items()))
+                        for dimension in self.drilldown}
         seen_none = set()
         for cells in rows:
             # Keep "empty" dimension value if there is a non-zero measure associated
@@ -341,7 +342,7 @@ class Visualization(object):
 
     def __iter__(self):
         if self.loop:
-            members = list(self.loop.members)
+            members = list(self.loop.members(self.filters.items()))
             for member in members:
                 table = self.copy()
                 table.loop = None

bijoe/visualization/views.py

@@ -320,7 +320,7 @@ class VisualizationJSONView(generics.GenericAPIView):
             axis = loop[0]['axis']
         else:
             axis = loop[0]['axis']
-            axis['loop'] = [x.label for x in all_visualizations.loop.members]
+            axis['loop'] = [x.label for x in all_visualizations.loop.members(all_visualizations.filters.items())]
             data = [x['data'] for x in loop]
 
         unit = 'seconds' if all_visualizations.measure.type == 'duration' else None

tests/fixtures/schema2/01_schema.json

@@ -31,7 +31,8 @@
                     "order_by": "category.label",
                     "type": "integer",
                     "value": "category.id",
-                    "value_label": "category.label"
+                    "value_label": "category.label",
+                    "filter_in_join": true
                 },
                 {
                     "join": [
@@ -9523,4 +9524,4 @@
     "search_path": [
         "public"
     ]
-}
+}

tests/test_schema1.py

@@ -104,9 +104,8 @@ def test_item_dimension(schema1, app, admin):
     form.set('filter__outersubcategory', ['__none__'])
     response = form.submit('visualize')
     assert get_table(response) == [
-        ['Outer SubCategory', u'sub\xe94', u'sub\xe95', u'sub\xe96', u'sub\xe98',
-         u'sub\xe99', u'sub\xe97', u'sub\xe92', u'sub\xe93', u'sub\xe91', 'Aucun(e)'],
-        ['number of rows', '0', '0', '0', '0', '0', '0', '0', '0', '0', '1']
+        ['Outer SubCategory', 'Aucun(e)'],
+        ['number of rows', '1']
     ]