You are here

Altering views queries

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');
  }
}

Tags

Drupal 7 views query alter sql MySQL

Comments

Dude, I spent hours trying to do exactly this, you saved my day! The solution works great, many manu thanks!

Add new comment