0

I have extending a query function for a D2D migration. The nodes have a field on them for a certain type of ID. In the old D6 site the the field did not have to be unique, but in the new D7 site the field does have to be unique.

I want to use db_select so that I only get the newest nodes in the content_type_nodetype table if there are multiple nodes with the same value for that id field.

Sample table data:

content_type_nodetype
____________________________
| nid | field_someid_value |
|==========================|
| 1   | 12345              |
| 2   | 12346              |
| 3   | 12347              |
| 4   | 12345              | 

node
____________________________
| nid | created            |
|==========================|
| 1   | 1292780734         |
| 2   | 1292780735         |
| 3   | 1292780736         |
| 4   | 1292780737         |

Given that data, the query I want should only return nid 2,3, and 4 as the value for field_someid_value for nodes 1 and 4 are the same, but the created timestamp in the node table for nid 4 is later than the timestamp for 1.

1
  • 1
    Use range & orderBy to achieve your goal.
    – mikeytown2
    Sep 8, 2014 at 18:30

2 Answers 2

1

You might try something like :

$query = db_select('node', 'n');
$query->join('content_type_nodetype', 'ct', 'ct.nid = n.nid');
$query->groupBy('field_someid_value');
$query->addExpression('MAX(n.nid)', 'nid');

The 'nid' order must follow the 'created' order so you won't need to order the results. Just taking the biggest nid for each field_someid_value should do the job.

1
  • The MAX expression would only return one value where in the migration I need to return all of the latest values.
    – CR47
    Sep 9, 2014 at 15:10
0

It turns out there was another (custom) table in my legacy database which held another set of data for those specific IDs and the node ID's associated. All I had to do was join with that table on the specific ID and set a condition for the nid to equal the nid above.

Your Answer

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

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