How do I alter a view query to use "OR"?

Question

The following is the content of the $query object that is passed to a view.

[where] => Array(
  [0] => Array(
    [clauses] => Array(
      [0] => node.type in ('%s')
      [1] => node.status  0
      [2] => node.promote  0
      [3] => node.sticky  0
    )
    [args] => Array(
      [0] => test
    )
    [type] => AND
  )
)
[having] => Array()
[group_operator] => AND

I want to override it by using hook_views_query_alter(). The default operators are all AND, but I want to get the following query

[0] => node.type in ('%s') AND
[1] => node.status  0 AND
[2] => node.promote  0  OR
[3] => node.sticky  0

How do I make the last operator to use "OR"?

db_query("SELECT content_id, count(*) AS cnt FROM {votingapi_vote} WHERE value =1 AND value_type = '%s'" , "option");

How do I use the query in hook_views_query_alter()?

Answer 1

This is untested, but I think what you want to do within your alter function is along the lines of this:

function mymodule_views_query_alter(&$query) {
  unset($query->where[0]['clauses'][2]);
  unset($query->where[0]['clauses'][3]);

  $query->where[1] = array(
    'clauses' => array(
      0 => 'node.promote <> 0',
      1 => 'node.sticky <> 0',
    ),
    'type' => 'OR',
  );
}

This is essentially moving the promote and sticky clauses into a new where grouping, and by setting the type to 'OR', it should work the way you outline in your question.

Answer 2

I think you can use db_or, for example if you are creating a new where clause you can use something like this

function mymodule_views_query_alter(&$query) {
  if ('name' == $view->name && 'page' == $view->current_display) {
    $or = db_or()
    ->condition('table.column', $value, 'NOT IN')
    ->condition('table.column', $value, 'NOT IN');
    $query->add_where(1,$or);
  }
}

For your problem you may need to use foreach and find the exact case to use db_or

Answer 3

I realize this is old but I thought I'd answer it for anyone who needs it.

Views 7.x-3.x. does this through the UI. You simply add the filters and then through the filter UI, you can order the filters and add additional groups of filters that you can arrange in AND or OR relationships.

In this case I would add a second group in an AND relationship with the first group but with an OR relationship with the contents of the group, which would consist of: node.promote <> 0 OR node.sticky <> 0.