drupal get node revision by timestamp

Question

I'm building a module that gets the value of a custom field based on a timestamp that a user clicked on it in the past.

To not make it too specific and complex I simplified my question so other users can use this as well.

Basically I'm selecting from the pop_links_stats ( has the timestamp ), node, node_revision, and field_revision_field_custom tables:

$query2 = db_select('pop_links_stats', 'p');    
$query2->join('node', 'n', 'n.nid = p.nid');
$query2->join('node_revision', 'nr', 'nr.nid = n.nid');
$query2->join('field_revision_field_custom', 'f', 'nr.vid = f.revision_id');

I already have the $nid so I set the condition to that $nid

$query2->condition('n.nid', $node->nid);

grouping by so it will only select one 'click' per day, per node, per ip ( added the following for the completeness. I need this but it is somewhat unrelated to my question so ignore the groupBy if this is not causing any problems. )

$query2->groupBy('DATE(FROM_UNIXTIME(p.timestamp))')->groupBy('n.nid')->groupBy('p.hostname');

selecting the fields I need ( for now just the revision id) :

$query2->fields('nr', array('vid'));

So my guess is to join the node_revision table and selecting the max vid where nr.timestamp <= p.timestamp and n.nid = nr.nid OR 'selecting' the max nr.timestamp where nr.timestamp <= p.timestamp

But I'm having trouble implementing that in the drupal DB API

EDIT:

Here's a query I've used in mySQL which gives me the result I want:

SELECT p.cid, FROM_UNIXTIME(p.timestamp), FROM_UNIXTIME(nrev.timestamp), n.nid, nrev.vid FROM pop_links_stats p
JOIN node n ON n.nid = p.nid
JOIN node_revision nrev ON nrev.vid = (SELECT max(vid) FROM node_revision nr WHERE nr.nid = p.nid AND nr.timestamp <= p.timestamp )
GROUP BY DATE(FROM_UNIXTIME(p.timestamp)), p.nid, p.hostname

So what I'm looking for is how to make the join in:

JOIN node_revision nrev ON nrev.vid = (SELECT max(vid) FROM node_revision nr WHERE nr.nid = p.nid AND nr.timestamp <= p.timestamp )

Answer 1

There doesn't seem to be a structured way to shoehorn an advanced join like that into an API query...so you'll probably just have to go manually:

$query = db_select('pop_links_stats', 'p')
  ->fields('p', array('cid'))
  ->fields('n', array('nid'))
  ->fields('nrev', array('vid'))
  ->groupBy('DATE(FROM_UNIXTIME(p.timestamp))')
  ->groupBy('p.nid')
  ->groupBy('p.hostname');

$query->addExpression('FROM_UNIXTIME(p.timestamp)', 'p_timestamp');
$query->addExpression('FROM_UNIXTIME(nrev.timestamp)', 'nrev_timestamp');

$query->join('node', 'n', 'n.nid = p.nid');
$query->join('node_revision', 'nrev', 'nrev.vid = (SELECT max(vid) FROM {node_revision} nr WHERE nr.nid = p.nid AND nr.timestamp <= p.timestamp )');

That produces:

SELECT p.cid AS cid, n.nid AS nid, nrev.vid AS vid, FROM_UNIXTIME(p.timestamp) AS p_timestamp, FROM_UNIXTIME(nrev.timestamp) AS nrev_timestamp
FROM 
{pop_links_stats} p
INNER JOIN {node} n ON n.nid = p.nid
INNER JOIN {node_revision} nrev ON nrev.vid = (SELECT max(vid) FROM node_revision nr WHERE nr.nid = p.nid AND nr.timestamp <= p.timestamp )
GROUP BY DATE(FROM_UNIXTIME(p.timestamp)), p.nid, p.hostname