3

I've noticed that the following does not work to unpublish nodes:

db_update('node')
  ->fields(array('status' => 0))
  ->condition('nid', $array_of_nids, 'IN')
  ->execute();

But it does if you follow up with the same update on the node_revision table. (But perhaps I should only be updating the status of the current revision (vid)?)

I found this confusing because the nodes in question do not use revisioning yet it seems that the revision table holds more authoritative data than the main node table (so if so, what's the point of node table?)

Can anyone explain how node and node_revision are related and therefore how to do a bulk update such as this 'properly'? (I'd prefer to avoid a costly loop of node_load, node_save if poss.) I'd like to understand the Drupal Way here.

(I have read node revisions which covers D6, but that did not give me the understanding I was after.)

4

The node table holds the basic information about the node, the nid is the primary key on this table and contains the vid field which is a foreign key to the node_revision table. The node_revision table holds each revision of the node, the vid is the primary key on this table and contains the nid field which is a foreign key to node table.

You may think that this information is identical, but it's not. The node table only contains one record relating to a node and also stores the current vid whereas the node_revision table contains the revision data. Just because you're not using revisions it doesn't mean that this process isn't being used.

Also keep in mind that all field tables have 2 versions, field_data_[field_name] and field_revision_[field_name], the data tables contain the current revision and the revisions table contain the revision data.

To get an understanding of how practical revisioning works try looking at Revisioning, a more complex and powerful solution can be achieved using Workbench

  • Yes. But as it's not optional to use the revisions system, why is the data duplicated in two tables (more including the fields tables) if they are both always required? – artfulrobot Sep 14 '14 at 18:02
  • Because the revision system is always available. Go to edit any node and look under the Revision information tab. – Colin Shipton Sep 15 '14 at 8:37
  • I understand that it's not optional; that all nodes and all fields use revisions; I get that. But I don't see the point from a DBA POV of having the same data in two tables - perhaps the redundancy is for query optimisation (saves a join) in certain circumstances? – artfulrobot Sep 15 '14 at 10:57
  • 1
    In my experience it's best not to think of Drupal from a DBA... – Colin Shipton Sep 15 '14 at 21:22
  • umm...I have a hard time believing the answer is 'core developers just make arbitrary decisions'. Found an issue that explains this in some detail drupal.org/node/2083451. – digitgopher Dec 11 '15 at 19:36
0

Node revision handles the edits in the node time to time so if you want to unpublish any node just try unpublishing the node by node_load();

<?php
$node = node_load($nid);
$node->status=0;
node_save($node);
?>

It will update the status in all the tables automatically.

If you don't want to use default methods.

Its the best way to update any node, hoewever if you want to update any node data using query then you need to update it in both the tables(node and node_revision)

Also revision is to keep backup of old data and create a new edited copy. Drupal will pick the data from the latest revision( not the older one) because there can be multiple revision created with each edit. If it is disabled there will be only one revision entry same as the node table

It can be seen in the top right bar in the node edit area when any of its revision is created.

Revisions can be controlled by admin/structure/types/manage/[content type name] against Publishing options tab to make it enabled/disabled by default

enter image description here And later it node/edit area it has a separate tab to enable/disable it

  • The OP explicitly said "I'd prefer to avoid a costly loop of node_load, node_save if poss." – Colin Shipton Sep 15 '14 at 8:38
  • @Colin, - Answer updated – Ashish T Sep 15 '14 at 10:10

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.