How to subtract two columns with db_select()?

Question

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"

Answer 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)

Answer 2

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));