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 .

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. – Sanjok Gurung May 23 '17 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 '17 at 16:57
  • Yea I upvoted too early. This doesn't work on another conditions, would you know to use where.?? – Sanjok Gurung May 23 '17 at 17:00
  • Did you see the updated answer? – rjl May 23 '17 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. – Sanjok Gurung May 23 '17 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, privacy policy and cookie policy

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