1

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 )
  • 2
    Your best bet would be to get the query working in standard MySQL first, then convert it to Drupal's API...if you've got that far could you post the desired query? It'll make it easier to spot where the issue is – Clive Jun 3 '13 at 9:30
  • updated my question with the mysql query – FLY Jun 3 '13 at 13:16
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
  • Thanks! In the sub select query node_revision is not included inside '{}' so when using a prefix in the table name isn't prefixed. Did you use a tool to generate the db_select query? your answer was very quick ;) – FLY Jun 3 '13 at 14:17
  • 1
    Good catch, I missed that :) Unfortunately there's no tool to generate that sort of code, just have to rely on the ol' brain! – Clive Jun 3 '13 at 14:23

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.