Back in the Drupal 6 days, writing a custom Views query was as easy as $view->build_info['query']. However, because Views is now using Drupal's new query generator, the query is a SelectQuery object which you will have to modify or replace. Fortunately, there is some good documentation around dynamic queries to get you started.
Views lets you do a lot, but there are still situations (in my mind at least) when conditions require a custom rewrite. The scenario was involving a content type for graphic ads that have a single image, referenced by multiple nodes. A random set of 3 graphic ads choosing an individual Image only once was to be generated.
- Node 1, Image 1, URL 1
- Node 2, Image 1, URL 2
- Node 3, Image 1, URL 3
- Node 4, Image 2, URL 4
- ...
- and so on
To get started I made a simple site_tweaks module and added hook_views_api() to register views hooks with the custom module:
/*
* Implementation of hook_views_api()
*
* Register View API information.
*/
function site_tweaks_views_api() {
return array(
'api' => 3,
);
}
The second part (for me) was to generate the query that couldn't be produced through views. Here is the nested select I came up with:
SELECT * FROM (
SELECT field_data_field_image.field_image_fid, node.nid
FROM node
LEFT JOIN field_data_field_image field_data_field_image ON node.nid = field_data_field_image.entity_id AND (field_data_field_image.entity_type = 'node' AND field_data_field_image.deleted = '0')
INNER JOIN file_managed file_managed_field_data_field_image ON field_data_field_image.field_image_fid = file_managed_field_data_field_image.fid
INNER JOIN field_data_field_graphic_ad_type field_data_field_graphic_ad_type ON node.nid = field_data_field_graphic_ad_type.entity_id AND (field_data_field_graphic_ad_type.entity_type = 'node' AND field_data_field_graphic_ad_type.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('graphic_ad')) AND (field_data_field_graphic_ad_type.field_graphic_ad_type_tid IN ('', '32', '35', '31')) ))
ORDER BY RAND()
) AS T
GROUP BY field_image_fid
ORDER BY RAND()
Finally, the true task of converting the query to utilize SelectQuery using hook_views_pre_execute(). The following code was placed in site_tweaks.views.inc:
/*
* Implementation of hook_views_pre_execute()
*/
function site_tweaks_views_pre_execute(&$view) {
if($view->name == "graphic_ads" && $view->current_display == "block_23") {
$sub_query = db_select('node', 'n');
$sub_query->leftJoin('field_data_field_image', 'fdfi', 'n.nid = fdfi.entity_id AND (fdfi.entity_type = :entity_type AND fdfi.deleted = :deleted)',
array(
':entity_type' => 'node',
':deleted' => 0,
)
);
$sub_query->innerJoin('file_managed', 'fm', 'fdfi.field_image_fid = fm.fid');
$sub_query->innerJoin('field_data_field_graphic_ad_type', 'fdfgat', 'n.nid = fdfgat.entity_id AND (fdfgat.entity_type = :entity_type AND fdfgat.deleted = :deleted)',
array(
':entity_type' => 'node',
':deleted' => 0,
)
);
$sub_query
->fields('fdfi', array('field_image_fid'))
->fields('n', array('nid'))
->condition('n.status', '1')
->condition('n.type', 'graphic_ad')
->condition('fdfgat.field_graphic_ad_type_tid', array(32, 35, 31), 'IN')
->orderRandom();
$query = db_select($sub_query, 'T');
$query
->fields('T')
->groupBy('field_image_fid')
->orderRandom();
$view->build_info['query'] = $query;
}}
And viola. Good luck with your custom Views query!
