3

I have a field in my users' profiles that has been defined as varchar initially even though it holds numeric values. I'm trying to use that field as an exposed filter and perform "greater than or equal" operation on it; however, since it's a varchar field, View doesn't give me that option.

In my module, I have come up with these two hooks to do a workaround:

function MY_MODULE_views_query_alter(&$view, &$query) {
    if ( $view->name == 'manage_users' ) {
        foreach($query->where[1]['conditions'] as $key => $condition) {
            if ( $condition['field'] == 'field_data_field_count.field_count_value' ) {
                $query->where[1]['conditions'][$key]['value'] = (int)$query->where[1]['conditions'][$key]['value']; 
                $query->where[1]['conditions'][$key]['operator'] = '>=';
                break; 
            }
        }
    }
}

function MY_MODULE_views_data_alter(&$data) {
    $data['field_data_field_count']['field_count_value']['filter']['handler'] = 'views_handler_filter_numeric';  
    $data['field_data_field_count']['field_count_format']['filter']['handler'] = 'views_handler_filter_numeric';
    $data['field_data_field_count']['field_count_value']['argument']['handler'] = 'views_handler_filter_numeric';  
    $data['field_data_field_count']['field_count_format']['argument']['handler'] = 'views_handler_filter_numeric';
}

The first hook seems to be working fine and successfully changing the operator from LIKE to >=; however, that's not enough for me because it still thinks 2 is greater than 15. So I added the second hook to make sure the field is actually treated as numeric, but that doesn't seem to have any effect on how the results are returned and it still thinks 2 is greater than 15.

2
  • In the final SQL query a string and integer is being compared which will result in a string to string comparison. You need to cast the string to an unsigned which does not seem possible: Drupal Views alter Filter to CAST() string as Float May 28, 2016 at 7:25
  • Hey @J.Reynolds, I came up with an answer to this question, as well as the one you mention from S.O., added an answer there too. May 30, 2016 at 7:57

3 Answers 3

3

Excellent question!
You only need the hook_views_query_alter function, here is what it should look like:

function MY_MODULE_views_query_alter(&$view, &$query) {
  if ( $view->name == 'manage_users' ) {

    // For filtering
    foreach($query->where[1]['conditions'] as $key => $condition) {
      if ( $condition['field'] == 'field_data_field_count.field_count_value' ) {
        $query->where[1]['conditions'][$key]['value'] = array(':val' => (int)$query->where[1]['conditions'][$key]['value']); 
        $query->where[1]['conditions'][$key]['field'] = 'CAST(' . $query->where[1]['conditions'][$key]['field'] . ' AS UNSIGNED) >= :val';
        $query->where[1]['conditions'][$key]['operator'] = 'formula';
        break; 
      }
    }

    // For sorting as well
    foreach($query->orderby as $key => $condition) {
      if ( $condition['field'] == 'field_data_field_count_field_count_value' ) {                
        $query->orderby[$key]['field'] = 'CAST(' . $query->orderby[$key]['field'] . ' AS UNSIGNED)'; 
        break; 
      }
    }
  }
}

Most important parts in this code snippet:

  1. Using CAST to change the varchar into an integer [doc]
  2. Using the 'formula' operator to instruct views that you are using a complex SQL expression, check the source code of add_where_expression! [doc]

Good luck!


Update Note: Even though this is working fine, the more elegant/readable way would be to have a function (equivalent to add_where_expression) that would allow overwriting a where/having condition.

5
  • Brilliant! So the trick was in setting operator to formula. May 30, 2016 at 15:21
  • This 'fix' is addressing the symptom and not the issue. You should be recreating the field correctly. If you have data for it already you should be fixing the database. There are several tried and true methods of doing so. It will be less maintainable this way.
    – sareed
    Jun 3, 2016 at 15:56
  • Hi @sareed : In the frame of the OP, this is a fully justified answer, focusing on the code problem presented by the user. In the frame of an alternative way to resolve the issue at its core (on the database level), the answer from No Sssweat is describing all the needed steps to fix it. You could add a positive comment to that answer and upvote it. Or even provide this post with yet another alternative alswer in order to enrich it. That way eveyone will benefit. Cheers! Jun 3, 2016 at 16:33
  • I meant my comment as a disclaimer to benefit people who see it. "that has been defined as varchar initially even though it holds numeric values" tells me it is not a code but a configuration issue. To create another similar view you would have to go back and change the code snippet to include the new view as well. This creates a maintenance issue because you should not have to go to code every time you include this field as a filter. It could also cause problems elsewhere. Instead, fix the field.
    – sareed
    Jun 3, 2016 at 16:48
  • 1
    Tell the person that asked the question how to "fix the field", a detailed answer is better than a disclaimer placed in a comment. Jun 3, 2016 at 18:47
1

Alternatively, if the field is meant to hold numeric data why don't you just update the db so that the field is numeric?

Are you saying directly changing it from a varchar to an int? Would that not cause any issues? Should I still keep the "_format" column after I change the data type?

If you want to be safe, you can do it like this:

1) Go to your database and export your text field as a CSV file.

Note: If you're using for your export, do not use CSV for MS Excel. Use the CSV option. As CSV for MS Excel displays the values in a comma format rather than columns. enter image description here

2) Then open your exported .CSV file with Excel.

3) In Drupal delete the text field and create a new integer field.

4) Go to /user/1/edit and enter a number in your decimal field (do not enter 1 as you will not know which one is the uid and which one is the actual field) and save changes (this is so when we export we have the table structure)

5) Go back to your database and find the new decimal field, export as CSV. Then open on Excel.

6) Use the values from your 1st Excel to copy the node id's and the field values and paste them onto your 2nd Excel in the appropriate column(s) and save.

7) Import 2nd Excel to your integer field and check-mark ☑ replace table data with file.

8) In Drupal, clear/flush the cache to see the changes.

0

You could try using a hook_views_data_alter and changing the field and filter handlers to views_handler_field_numeric and views_handler_filter_numeric, that should work pretty well. I haven't personally tried it in hook_views_data_alter but the technique works great in hook_views_data.

Alternatively, if the field is meant to hold numeric data why don't you just update the db so that the field is numeric?

Assuming this is a site that is in production I would suggest using hook_update_N and db_update to change the type column for the field in field_config to number_integer and the module field to number. Definitely test this update locally before pushing to the main branch.

2
  • Are you saying directly changing it from a varchar to an int? Would that not cause any issues? Should I still keep the "_format" column after I change the data type?
    – farjam
    May 23, 2016 at 19:02
  • If you use the update system correctly it should not cause any issues with a production site. This is assuming the you are sure the field only contains numeric data. If the site is not in production yet you can just change the db directly and test to make sure there are no issues.
    – sareed
    May 23, 2016 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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