0

I am strugginling to find out how to execute the below query using drupal's db api

$startdate = '1491001200';
$enddate = '1498690799';
"SELECT * FROM field_data_field_symposium_event_time
    WHERE UNIX_TIMESTAMP(field_symposium_event_time_value) BETWEEN
    $startdate AND $enddate;"

I have tried adding 'addExpression' but does not work. I did it like this

$query = db_select('node','n');
$query->join('field_data_field_symposium_event_time', 'et', 'et.entity_id = n.nid');    
$query->condition('et.field_data_field_symposium_event_time_value',array($day_start, $day_end), 'BETWEEN'));
$query->addExpression('UNIX_TIMESTAMP(et.field_symposium_event_time_value)');
$query->fields('et',array('field_symposium_event_time_value'));
$result = $query->execute()->fetchAll();

The $result is empty. Is there a way to achieve this as I dont want to write the full query .

2 Answers 2

3

I believe the addExpression is for SELECT clause expressions

https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21Select.php/function/Select%3A%3AaddExpression/8.2.x

For a WHERE clause expression it should be

$query->where('UNIX_TIMESTAMP(et.field_symposium_event_time_value) BETWEEN :start AND :end', array(':start' => $day_start, ':end' => $day_end));

https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21QueryConditionTrait.php/function/QueryConditionTrait%3A%3Awhere/8.2.x

And then of course remove your $query->condition(...); line.

9
  • Thank you exactly what I needed. I still dont understand the concept of addExpression.
    – ShanjayG
    May 23, 2017 at 16:45
  • Thanks for the up-vote, but I am wondering if that actually worked. I suddenly realized that addExpression() is for the SELECT cause, and you can use where() for WHERE clause expressions. api.drupal.org/api/drupal/… I will update the answer
    – rjl
    May 23, 2017 at 16:57
  • Yea I upvoted too early. This doesn't work on another conditions, would you know to use where.??
    – ShanjayG
    May 23, 2017 at 17:00
  • Did you see the updated answer?
    – rjl
    May 23, 2017 at 17:05
  • I did, this somehow is not working, although it could be because of the format of $start_date and $end_date. they are generated like this $start_date = strtotime(date('Y-m-d 00:00:00', strtotime(04/05/2017))); $end_date = strtotime(date('Y-m-d 00:00:00', strtotime(10/05/2017))); while the field_symposium_event_time_value is in 2017-05-05 17:30:00 format.
    – ShanjayG
    May 23, 2017 at 17:14
0

I think that ->addExpression is about adding SQL snippets like yours to modify selected columns right after the SELECT (vs. adding expressions to a WHERE clause).

The ->where() (and ->having()) clauses are what you want.

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.