Treating string field as numeric to use as an exposed filter

by farjam   Last Updated May 26, 2016 09:03 AM

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.



Answers 3


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.

sareed
sareed
May 23, 2016 17:44 PM

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.

No Sssweat
No Sssweat
May 26, 2016 03:59 AM

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.

Stefanos Petrakis
Stefanos Petrakis
May 29, 2016 21:20 PM

Related Questions


Updated February 22, 2017 12:07 PM

Updated May 02, 2015 23:03 PM

Updated March 27, 2015 14:18 PM

Updated May 01, 2016 08:03 AM

Updated March 03, 2016 02:03 AM