It took me a good bit of time to figure out how to use hook_views_query_alter to modify my view query so I just want to pass on some small bit of knowledge to others who may find it helpful. I wanted to create a view/block that displayed a list of books on my site. I only wanted to list the names of parent book pages not all book pages just book titles. Out of the box, the views module can create a view to display nodes of type "book" but that includes all book pages. The query looked like this:

SELECT node.created AS node_created, node.nid AS nid
FROM
node
WHERE (( (node.status = '1') AND (node.type IN  ('book')) ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

What I really wanted was this query that joined on the book table using book.nid.

SELECT node.created AS node_created, node.nid AS nid
FROM
node
LEFT JOIN book ON node.nid = book.nid
WHERE (( (node.status = '1') AND (node.type IN  ('book')) AND (book.nid  = book.bid) ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

I was able to create the latter query using the hook_views_query_alter. I spend most of the day reading about the views_plugin_query_default api and views_object and tried several query object function calls. Along the trip, I learned how to use the devel module and dpm to display my query in the admin. I tried all kinds of stuff and ended on this two line module.

function my_hook_views_query_alter(&$view, &$query){
  if ($view->name == 'my_hook') {
    $alias = $query->add_table('book','node');
    $query->add_where_expression(0,'book.nid = book.bid');
    dpm($query); //dumps object to admin with devel module
  }
}

The add_table call automatically analyzed the relationship and created the LEFT JOIN above. I had played around with the views_plugin_query_default::add_where but the result was only a simple where clause that only allowed a scalar value (e.g. book.nid = 'book.bid'). The add_where_expression uses the condition type 'formula' to create a complex where. The resulting where clause that just joins on book.bid was :

WHERE (( (node.status = '1') AND (node.type IN ('book')) AND (book.nid = book.bid) ))

Thank you, thank you.

I hope this helps the next newbie Drupalite like me and don't lose any hair.

Comments

holtzermann17’s picture

I've included the debugging code I used.

function planetmath_blocks_views_query_alter(&$view, &$query) {
  switch($view->name) {
    case 'problems':  
      $contextual_filter=$view->argument['nid']->value[0];
      // removing this thing by number is EXTREMELY hackish,
      // but it's the only thing that made sense to do in the current circumstances
      unset($query->where[0]['conditions'][2]);
      if(is_numeric($contextual_filter)) {
	//dd("ADDING JOIN!");
	$join = new views_join;
	$join->construct('article_has_problem','node','nid','pid',NULL,"INNER");
	$query->add_relationship('ahp',$join,'article_has_problem');
	$query->add_where(0,"ahp.aid","$contextual_filter","=");
      }
      //$selectQ = $query->query();
      //dpm((string)($selectQ));
      //dpm($query);
      break;
  }
}

xref: Documentation for the construct, add_relationship, and add_where functions used in the above.