1

I have a db_select query that outputs two columns. I want to find the difference between these columns, output to another column, and then put a condition on this third column.

Here is a mysql query that I want to convert into db-select:

"SELECT *, `column1value`-`column2value` AS `difference` FROM `mytable` WHERE `difference` > 55"

2 Answers 2

1

You're looking for MySQL's HAVING, which permits SELECT level aliases.

So this:

SELECT *, `column1value`-`column2value` AS `difference` 
FROM `mytable` 
WHERE `difference` > 55

Becomes this:

SELECT *, `column1value`-`column2value` AS `difference` 
FROM `mytable` 
HAVING `difference` > 55

Which in Drupal-ese is:

$query = db_select('mytable')
  ->fields('mytable')
  ->having('difference > :floor', array(':floor' => 55));

$query->addExpression('column1value - column2value', 'difference');

For a simple calculation like this example performance would be better using the expression directly in a WHERE clause. In the example you've given in your answer, I suspect HAVING would be quicker (although MySQL's pretty smart, it may be able to cache that calculation)

0

It turns out that you can't use an MySQL expression in a conditional: the above MySQL doesn't work.

Here is an example db_select() query that I came up with, that finds the difference between two flagging timestamps, and then makes sure that it's between two thresholds. I'm concerned that it's calculating the difference 3 times, but there doesn't seem to be a way around it, since the "duration" alias can't be used anywhere else, not even with raw MySQL.

$query = db_select('node', 'n');
$query->leftJoin('flagging', 'fass', 'fass.entity_id = n.nid AND fass.fid = :assigned', array(':assigned' => $task_assigned_fid));
$query->leftJoin('flagging', 'fcom', 'fcom.entity_id = n.nid AND fcom.fid = :completed', array(':completed' => $task_completed_fid));
$query->condition('fcom.timestamp', $this->date_endpoint0, '<');
$query->condition('fcom.timestamp', $this->date_endpoint1, '>=');
$query->condition('n.type', 'task', '=');
$query->isNotNull('fass.uid');
$query->isNotNull('fcom.uid');
$query->fields('n', array('nid'));
$query->fields('fass', array('timestamp'));
$query->fields('fcom', array('timestamp'));

// Note that we are calculating the difference three times.  I'd prefer to do it once.
$query->addExpression('fcom.timestamp - fass.timestamp', 'duration');
$query->where('(CAST(fcom.timestamp as SIGNED) - CAST(fass.timestamp as SIGNED)) < :threshold0', array(':threshold0' => $threshold0));
$query->where('(CAST(fcom.timestamp as SIGNED) - CAST(fass.timestamp as SIGNED)) > :threshold1', array(':threshold1' => $threshold1));

Your Answer

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

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