3

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';
    }
  }
}
4
+50

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';
    }
0

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.

  • I will give this a try, though what I have above is based on what I have seen in drupal variable dumps. – Nalum Sep 5 '11 at 20:25
  • This doesn't seem to be working, the query has changed when I do a dump after my changes, but within the function. Don't think it is being used though. – Nalum Sep 6 '11 at 9:08
0

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.

  • I have tried these today also and had no luck with them. Thanks for the input though. – Nalum Sep 6 '11 at 15:25

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.