I'm sure there's an easy way to do the following but I've not come up with a safe Drupal way to do so:
My current front page has a view which generates a list of the most recent three articles on my site using the date stored in field_published attached to each article. The SQL which the view generates looks like this:
SELECT field_data_field_published.field_published_value AS field_data_field_published_field_published_value, node.nid AS nid FROM node node LEFT JOIN field_data_field_published field_data_field_published ON node.nid = field_data_field_published.entity_id AND (field_data_field_published.entity_type = 'node' AND field_data_field_published.deleted = '0') WHERE (( (node.status = '1') )) ORDER BY field_data_field_published_field_published_value DESC LIMIT 3 OFFSET 0;
( You can see what this looks like live on my website )
Running the query on mysql CLI generates this:
+--------------------------------------------------+-----+
| field_data_field_published_field_published_value | nid |
+--------------------------------------------------+-----+
| 2017-01-23 00:00:00 | 427 |
| 2017-01-01 00:00:00 | 426 |
| 2016-08-07 00:00:00 | 424 |
+--------------------------------------------------+-----+
I want to now create a similar view that searches only the month and day field of every article in the DB, matches the month and day against 'today', and generates a list of article nid. The SQL for this is:
SELECT field_data_field_published.field_published_value AS field_data_field_published_field_published_value, node.nid AS nid FROM node node LEFT JOIN field_data_field_published field_data_field_published ON node.nid = field_data_field_published.entity_id AND field_data_field_published.entity_type = 'node' WHERE (( (field_data_field_published.field_published_value LIKE (select date_format(curdate(),'____-%m-%d%'))) )) AND (( (node.status = '1') ))
ORDER BY field_data_field_published_field_published_value DESC LIMIT 3 OFFSET 0;
This correctly generates a list of article nid on the CLI:
+--------------------------------------------------+-----+
| field_data_field_published_field_published_value | nid |
+--------------------------------------------------+-----+
| 2015-05-24 00:00:00 | 369 |
| 1999-05-24 00:00:00 | 222 |
| 1997-05-24 00:00:00 | 150 |
+--------------------------------------------------+-----+
My question is, how do I get my functional SQL statement into a view?
I have tried using Views Contextual Filters and Granularity, but that does not allow me to omit the year when filtering dates.
I have tried to use hook views_pre_execute in a module but it barfs on my query: Something like
<?php
// TMG 3-21-09
// call to hook_views_pre_execute to override the query for the groups browser
function views_views_pre_execute(&$view) {
if($view->name=="future_front_page2") {
$kp_tih = date('____-m-d%');
$view->build_info['query']="SELECT field_data_field_published.field_pub
lished_value AS field_data_field_published_field_published_value, node.nid AS ni
d FROM node node LEFT JOIN field_data_field_published field_data_field_published
ON node.nid = field_data_field_published.entity_id AND field_data_field_publish
ed.entity_type = 'node' WHERE (( (field_data_field_published.field_published_val
ue LIKE (select date_format(curdate(),'____-%m-%d%'))) )) AND (( (node.status =
'1') )) ORDER BY field_data_field_published_field_published_value DESC LIMIT 3 O
FFSET 0";
$query = db_select('node','n')->addTag('node_access');
drupal_set_message(t($query));
Any suggestions?
Thanks in advance for any advice.
