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
    Use range & orderBy to achieve your goal. – mikeytown2 Sep 8 '14 at 18:30
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.

| improve this answer | |
  • The MAX expression would only return one value where in the migration I need to return all of the latest values. – CR47 Sep 9 '14 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.

| improve this answer | |

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.