Hi,
I got a view which display content data by it's relation to (base table) taxonomy term
I've added aggregation to content id (count) to avoid multiple rows.
At the end I've added a filter by content id, with numeric aggregation
The view uses distinct too.

It should show contents list related to the current nid by taxonomy terms.

When running in preview I get back a malformed sql query:

SELECT DISTINCT taxonomy_term_data.name AS taxonomy_term_data_name, taxonomy_term_data.vid AS taxonomy_term_data_vid, taxonomy_term_data.tid AS tid, taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name, field_argomento_taxonomy_term_data.title AS field_argomento_taxonomy_term_data_title, field_argomento_taxonomy_term_data.nid AS field_argomento_taxonomy_term_data_nid, field_argomento_taxonomy_term_data__field_data_field_sottotitolo.field_sottotitolo_value AS field_argomento_taxonomy_term_data__field_data_field_sottoti, taxonomy_term_data.weight AS taxonomy_term_data_weight, field_argomento_taxonomy_term_data.changed AS field_argomento_taxonomy_term_data_changed, MIN(field_argomento_taxonomy_term_data.nid) AS field_argomento_taxonomy_term_data_nid_1, 'node' AS field_data_field_sottotitolo_node_entity_type, COUNT(DISTINCT field_argomento_taxonomy_term_data.nid) AS field_argomento_taxonomy_term_data_nid_2
FROM 
{taxonomy_term_data} taxonomy_term_data
LEFT JOIN {field_data_field_argomento} field_data_field_argomento ON taxonomy_term_data.tid = field_data_field_argomento.field_argomento_tid
LEFT JOIN {node} field_argomento_taxonomy_term_data ON field_data_field_argomento.entity_id = field_argomento_taxonomy_term_data.nid
LEFT JOIN {taxonomy_vocabulary} taxonomy_vocabulary ON taxonomy_term_data.vid = taxonomy_vocabulary.vid
LEFT JOIN {taxonomy_index} field_argomento_taxonomy_term_data__taxonomy_index ON field_argomento_taxonomy_term_data.nid = field_argomento_taxonomy_term_data__taxonomy_index.nid
LEFT JOIN {field_data_field_sottotitolo} field_argomento_taxonomy_term_data__field_data_field_sottotitolo ON field_argomento_taxonomy_term_data.nid = field_argomento_taxonomy_term_data__field_data_field_sottotitolo.entity_id AND (field_argomento_taxonomy_term_data__field_data_field_sottotitolo.entity_type = 'node' AND field_argomento_taxonomy_term_data__field_data_field_sottotitolo.deleted = '0')
WHERE (( (taxonomy_vocabulary.machine_name IN  ('argomento')) ))
GROUP BY tid

-- 
-- HERE !!!!!
-- 
HAVING (( (COUNTDISTINCTfield_argomento_taxonomy_term_data__taxonomy_index.tid = '9') ))


ORDER BY taxonomy_term_data_weight ASC, field_argomento_taxonomy_term_data_changed ASC
LIMIT 5 OFFSET 0

I've started digging in code from views/handler/views_handler_argument_group_by_numeric.inc
and arrived to DatabaseCondition class, in compile method, where it expects an empty operator to keep as is the field name.

So, in views/handler/views_handler_argument_group_by_numeric.inc, lin 11
-$this->query->add_having(0, $field, $this->argument);
+$this->query->add_having(0, $field, array(), '');

This edit works as I expect, obviously there could be any sort of side effect :(
Is this a bug or something it's expected and I'm not using properly ?

Thanks, Luca

CommentFileSizeAuthor
#1 1443692.patch727 bytesdawehner
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

dawehner’s picture

dawehner
Primary language German
CreditAttribution: dawehner commented
Status: Active » Needs review
FileSize
1443692.patch727 bytes

Thanks for reporting and even given an idea how to fix it! this was a big help, really.

Here is a fix i think is more correct. It would be cool to get some feedback.

muka’s picture

muka CreditAttribution: muka commented

Yes, it works! Thank you

Just a question:
Does make sense to apply aggregation over a specific filter like this?

HAVING (( (COUNT(field_argomento_taxonomy_term_data__taxonomy_index.tid) = '9') ))

would return false, a part if the results are exactly 9!

Wouldn't be useful have a "nullable" aggregation settings in views_plugin_query_default::get_aggregation_info
It could be done (I suppose :J ) with my edit above which results in

HAVING (( (COUNT(field_argomento_taxonomy_term_data__taxonomy_index.tid)) ))

that could make more sense on argument filter and wouldn't require to much work (I suppose, again).
EDIT:
I've tried, just for fun 

// -- view_plugin_query_defauly.inc ~ line 1406
      'void' => array(
        'title' => t('Not empty'),
        'method' => 'views_query_default_aggregation_method_void',
        'handler' => array(
          'argument' => 'views_handler_argument_group_by_numeric_void',
          'field' => 'views_handler_field_numeric',
          'filter' => 'views_handler_filter_group_by_numeric',
          'sort' => 'views_handler_sort_group_by_numeric',
        ),
      ),

// -- view_plugin_query_defauly.inc ~ EOF
function views_query_default_aggregation_method_void($group_type, $field) {
    return 'COUNT(' . $field . ')';
}

then copied views_handler_argument_group_by_numeric and renamed,finally edited with my code above.
It works! It works!

Luca

dawehner’s picture

dawehner
Primary language German
CreditAttribution: dawehner commented
Status: Needs review » Active 

HAVING (( (COUNT(field_argomento_taxonomy_term_data__taxonomy_index.tid)) ))

I'mt not sure about your use case here. Why do you actually use contextual filters if you need something like this?
You can use normal filters with count() > 0, and it should return you what's needed.

I just committed the patch from above.

muka’s picture

muka CreditAttribution: muka commented

Mmh.. Ok, I could be doing something wrong.
But, I need aggregation for a field, but I get it "for free" for filter too..
The edit in #2 is skipping it in filter handler, as I know is the only way or not?