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
Here's another example
I've included the debugging code I used.
xref: Documentation for the construct, add_relationship, and add_where functions used in the above.