Posted by mrharolda on August 16, 2011 at 6:42pm
I have a tested and working query that I need to convert to the new Drupal 7 database layer, but I can't seem to find how to use a nested SELECT...
I need to join the MAX() value of last_totalcount to all rows in node_counter and this should be the way to do that:
SELECT nc.nid AS nid, nc.totalcount AS totalcount, last_totalcount AS last_totalcount
FROM node_counter nc, (
SELECT max(last_totalcount) AS last_totalcount, nid
FROM node_counter_history GROUP BY nid
) AS tbl
WHERE nc.timestamp >= 1309471200 AND tbl.nid = nc.nidD.o/Google only shows me how to use a subquery in condition(), but I don't need the subquery/subselect in the WHERE-clause, I need it nested in a SELECT.
What function can't I find?
<?php
// Fetch statistics totalcounts
$query = db_select('node_counter', 'nc')
->fields('nc', array('nid', 'totalcount'))
->condition('nc.timestamp', $from, '>=');
// Add the latest total counter
$subquery = db_select('node_counter_history', 'nch')
->fields('nch', array('nid'))
->addExpression('MAX(last_totalcount)', 'last_totalcount');
$query->condition($subquery); // WRONG!!! @TODO: find function!!! ;)
?>