1

I have a content type with a list(text) field. In which the admin can set multiple values on the field for the content type. Using a view the information is exposed to the end user. The views has a exposed filter to filter the list(text) field. However multiple options can be selected and need to be checked against the values set by the admin. The way Drupal views does this is by joining the table as many times as the amount of values being searched for, resulting in a very slow query.

I am now looking to do this with the query adjustment below inspired by this post: https://stackoverflow.com/questions/11636061/matching-all-values-in-in-clause

However I have no clue on how to include the HAVING combined with COUNT as suggested in de post from the link above within Drupal.

$field_values - array(1, 4)
$query->where[1]['conditions'][0]['field'] = 'field_data_field_listtext.field_data_field_listtext_value';
$query->where[1]['conditions'][0['value'] = $field_values;
$query->where[1]['conditions'][0['operator'] = 'IN';

Suggestions on solving the SQl query performance in other maners for exposed filters on list(text) are also very wecome.

  • A quick question: You are talking about a list of strings in your question. Why is the code example you provide dealing with integers? Or am I missing sth? Thanks – Stefanos Petrakis May 31 '16 at 17:50
  • Another thing, I just checked this following your description, having a List(text) with multiple values per node, and a view that allows multiple values to be selected. I don't see what you describe though in the generated SQL, that Drupal is "joining the table as many times as the amount of values being search for". I think I am missing more this time, hope you can elaborate. – Stefanos Petrakis May 31 '16 at 18:00
  • updated the code, your are right using integer in mij fieldname is confusing for the example. The code my view generates is: INNER JOIN field_data_field_listtext field_data_field_listtext_value_0 ON node.nid = field_data_field_listtext_value_0.entity_id AND field_data_field_listtext_value_0.field_listtext_comfort_value = '1' INNER JOIN field_data_field_listtext field_data_field_listtext_value_1 ON node.nid = field_data_field_listtext_value_1.entity_id AND field_data_field_listtext_value_1.field_listtext_comfort_value = '4' INNER JOIN field_data_field_listtext – DD dev May 31 '16 at 19:10
0

The following code should allow you to add the SQL code found in the accepted answer to the S.O. post you mentioned to the Views generated SQL code.

function YOUR_MODULE_views_query_alter(&$view, &$query) {
    if ( $view->name == 'YOUR_VIEW_NAME' ) {
        $value_count = 0;

        // Collect the number of selected values in the exposed filter
        foreach($query->where[1]['conditions'] as $key => $condition) {
            if ( $condition['field'] == 'field_data_field_listtext.field_data_field_listtext_value' ) {
                $value_count = sizeof($query->where[1]['conditions'][$key]['value']);
                break; 
            }
        }

        // Add the having expression
        if( $value_count > 0 ){
          $query->add_having_expression(
            0,
            "count(distinct :field_name) = :value_count", 
            array(
              ':field_name' => 'field_data_field_list_select.field_list_select_value', 
              ':value_count' => $value_count
            )
          );
        }
    }
}

Good luck!

  • thanks, i will try this in the weekend and let you know if it worked. – DD dev Jun 7 '16 at 7:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.