0

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.

1 Answer 1

0

Try to use hook_views_query_alter.

Even though, I think you can do it using only views without altering the query. Maybe using Date Views module would help.

2
  • I started off trying hook_views_query_alter but it similarly rejected the date calc in the SQL. Unable to find the Date Views module. Pointer? May 28, 2017 at 1:41
  • I'm not sure if you need the select at 'select date_format'. A similar query is : (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(my_field.created), SEC_TO_TIME(3600)), '%Y-%m-%d') = '2017-04-04'). If you want to add this module try to enable it using drush. drush en date_views -y
    – Ismini
    May 30, 2017 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.