Submitted by cableman on Sat, 12/14/2013 - 22:14
I needed to add a outer select query to a view query to remove double rows in the output, which came form the same node being related to two different Organic groups.
The query in questions was the one below, which is taken from views preview function. This could be any query from views that you wishes to alter, so this is just an example.
SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language,
node_og_membership.title AS node_og_membership_title,
node_og_membership.nid AS node_og_membership_nid,
node_og_membership.language AS node_og_membership_language,
field_data_field_ding_event_date.field_ding_event_date_value AS field_data_field_ding_event_date_field_ding_event_date_value,
'node' AS field_data_field_ding_event_date_node_entity_type,
'node' AS field_data_field_ding_event_list_image_node_entity_type,
'node' AS field_data_field_ding_event_lead_node_entity_type,
'node' AS field_data_field_ding_event_location_node_entity_type,
'node' AS field_data_field_ding_event_price_node_entity_type
FROM
{node} node
LEFT JOIN {og_membership} og_membership_node ON node.nid = og_membership_node.etid
AND og_membership_node.entity_type = 'node'
LEFT JOIN {node} node_og_membership ON og_membership_node.gid = node_og_membership.nid
AND og_membership_node.group_type = 'node'
LEFT JOIN {field_data_field_ding_event_date} field_data_field_ding_event_date ON node.nid = field_data_field_ding_event_date.entity_id
AND (field_data_field_ding_event_date.entity_type = 'node'
AND field_data_field_ding_event_date.deleted = '0')
INNER JOIN {taxonomy_index} taxonomy_index ON node.nid = taxonomy_index.nid
WHERE (( (node.status = '1')
AND (taxonomy_index.tid = '46')
AND (node.nid != '29' OR node.nid IS NULL) )
AND(( (node.type IN ('ding_event'))
AND (DATE_FORMAT(ADDTIME(field_data_field_ding_event_date.field_ding_event_date_value, SEC_TO_TIME(3600)), '%Y-%m-%d') >= '2013-12-14'
OR DATE_FORMAT(ADDTIME(field_data_field_ding_event_date.field_ding_event_date_value2, SEC_TO_TIME(3600)), '%Y-%m-%d') >= '2013-12-14') )))
ORDER BY field_data_field_ding_event_date_field_ding_event_date_value ASC
So I wanted to add an extra select around this query with a group by.
SELECT *
FROM
(....) inner_query
GROUP BY nid
To the rescue hook_views_pre_execute()
This code below simply substitutes the views query with a new select query with the original query as a sub-query.
/**
* Implements hook_views_pre_execute().
*/
function HOOK_views_pre_execute(&$view) {
if ($view->name == 'events' && $view->current_display == 'ding_event_list_same_tag') {
$inner = $view->build_info['query'];
$view->build_info['query'] = db_select($inner, 'inner_query')
->fields('inner_query')
->groupBy('nid');
}
}
Comments
Thanks!!!!!!!!
Add new comment