Customize a View 3 SQL query

Question

I've created a view that lists the content based on the first letter of that title matching an argument passed to the page. Now I'm trying to do the same, but with a 0-9 so when the user selects 0-9 the view gets all content types which begin with a number in the range 0-9.

I'm currently looking at hook_views_query_alter(), but I don't get back the results I am expecting. The function I've written is the following one:

function module_views_query_alter(&$view, &$query) {
  if ($view->name == 'custom_page' && $view->current_display == 'page_1') {
    if ($view->args[0] == '0-9') {
      $query->where[0]['conditions'][2]['field'] = 'SUBSTRING(node.title, 1, 1)';
      $query->where[0]['conditions'][2]['value'] = array('0','1','2','3','4','5','6','7','8','9');
      $query->where[0]['conditions'][2]['operator'] = 'in';
    }
  }
}

Answer 1

This is a bit of a cheat following the code that's used by the glossary view, but it works:

function module_views_query_alter(&$view, &$query) {
  if ($view->name == 'custom_page' && $view->current_display == 'page_1') {
    if ($view->args[0] == '0-9') {
      $query->where[0]['conditions'][2]['field'] =  "SUBSTRING(node.title, 1, :node_title) in ('0','1','2','3','4','5','6','7','8','9')";
      $query->where[0]['conditions'][2]['value'] = array(':node_title' => 1);
      $query->where[0]['conditions'][2]['operator'] = 'formula';
    }

Answer 2

In MySQL, when using the in() operator, you must past the list in a certain format. I'm not sure that views automatically converts PHP arrays to that format or not.

I know that when I've used in() in the past, I converted arrays myself. In your case it would look like this:

$query->where[0]['conditions'][2]['value'] = "('" . implode("','", range(0,9)) . "')";

You can see the results of the implode() & range() at this codepad.

Answer 3

since you want to select all the content titles that start with a number why not just put something like

ISNUMERIC(SUBSTRING(node.title, 1, 1)) 

or

BETWEEN 0 - 9 

in you WHERE statement.