How to use hook_views_query_alter to catch last DELTA of a multivalue date field? I need a View that should list only past events.

Events have multivalue date fields, so an event can have multiple "start" and "end" dates.

Date's API assign a DELTA value to every group of "start" and "end" dates.

In my configuration, an event is consideret "past" if its very last (= last delta) end date is &lt; of "now" (= today)

With default Views UI it seems impossible to catch LAST delta of group of dates for a record.

(see some answers by Merlinofchaos here: https://www.drupal.org/user/26979)

My UI views provides the following query:


SELECT field_data_field_period.delta AS field_data_field_period_delta, field_data_field_period.entity_id AS date_id_field_period2, field_data_field_period.delta AS date_delta_field_period2, node.created AS node_created, node.nid AS nid
FROM 
{node} node
LEFT JOIN {field_data_field_period} field_data_field_period ON node.nid = field_data_field_period.entity_id AND (field_data_field_period.entity_type = 'node' AND field_data_field_period.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('exhibition')) AND (DATE_FORMAT(ADDTIME(field_data_field_period.field_period_value2, SEC_TO_TIME(7200)), '%Y-%m-%d') &lt; '2014-07-16') ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0


With some tests with pure SQL directly on DB, I noticed that I can obtain the results I want with the following changes to the query:


SELECT MAX(field_data_field_period.delta) AS field_data_field_period_delta, field_data_field_period.entity_id AS date_id_field_period2, field_data_field_period.delta AS date_delta_field_period2, field_data_field_period.field_period_value2 AS field_data_field_period_field_period_value2, node.created AS node_created, node.nid AS nid
FROM node
LEFT JOIN field_data_field_period ON node.nid = field_data_field_period.entity_id AND (field_data_field_period.entity_type = 'node' AND field_data_field_period.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('exhibition')) ))
GROUP BY nid 
HAVING MAX(DATE_FORMAT(ADDTIME(field_data_field_period.field_period_value2, SEC_TO_TIME(7200)), '%Y-%m-%d')) &lt; '2014-07-15'


In the above please notice:


field_data_field_period.delta becomes MAX(field_data_field_period.delta)
Added clause "GROUP BY nid" and "HAVING MAX(DATE_FORMAT(ADDTIME(field_data_field_period.field_period_value2, SEC_TO_TIME(7200)), '%Y-%m-%d')) &lt; '2014-07-15' "


After hours of research I understood I have to use hook_views_query_alter to alter my views query, but the modification I need are very complex and I don't know where to start...

Please help me.
This can be done with HOOK_views_query_alter(). Add all the fields as normal within the views UI, including filtering on the date field value. Make sure the entity ID is in there (in your case, nid is in the query) and the date's delta field.

Next, add a WHERE clause on the delta in hook_views_query_alter() using add_where_expression():

function MODULE_views_query_alter(&amp;$view, &amp;$query) {
  // Check for correct view
  if ($view-&gt;name == 'view_name' &amp;&amp; $view-&gt;current_display == 'display_name') {
    $query-&gt;add_where_expression(0, 'field_data_field_period.delta = (SELECT MAX(field_data_field_period.delta) from field_data_field_period WHERE entity_id = nid)');
  }
}


This assumes the filter should be in the first filter group (0). 

References:

https://api.drupal.org/api/views/views.api.php/function/hook_views_query_alter/7.x-3.x

https://api.drupal.org/api/views/plugins!views_plugin_query_default.inc/function/views_plugin_query_default%3A%3Aadd_where_expression/7.x-3.x