Updating data on a drupal site.

Drupal provide a great API to create and amend field and its data. This comes very handy with updating data on existing website.

You must have experience where new fields need to be added to existing content types or amending the data of an existing fields. There are various ways to do so.

One way is to go through each node in an update hook and update the node data using drupal built in function like node_save(). This would only feasible with small data set. What if you have 20k nodes that need to be updated. Running a node_load and node_save on 20k nodes is not feasible on an update hook. It would grind your website to halt.

Another way is to use drupal queue, And on each cron run, process a certain amount of nodes based on time or number of nodes. This is lengthy process to update huge set of data.

Not to worry there is another fast way to do so, updating data straight in your database. Let’s have a look in detail how to do so.

Problem: We need to create a new boolean field named my_awsome_field for content type article and page. We also need to update existing node to have this field checked (boolean 1). And all this need to be done in one go, as we cannot rely to queue to process this data.

We will do this in an update hook, if you don’t know about update hook, read here

function My_module_update_7001() {
// first we need to check if this field doesn't exist
if (!field_info_field('my_awsome_field')) {
}
}

There are two part of creating a field. first we create field base:

    // Create the field base.
    $field = array(
    'field_name' => 'my_awsome_field',
    'type' => 'list_boolean',
   );
    field_create_field($field);

And then we create the field instance:

// we are going to insert this field in article and page Content Type
$bundle = array('article', 'page');
foreach ($bundles as $bundle) {
      // Create the field instance on the bundle.
      $instance = array(
        'field_name' => 'my_awsome_field',
        'entity_type' => 'node',
        'label' => 'My Awesome Field',
        'bundle' => $bundle,
        'widget' => array(
          'type' => 'options_onoff',
          'settings' => array('display_label' => 1),
          'weight' => 1,
        ),
      );
      field_create_instance($instance);
    }

 

Complete code upto here will look like:

function My_module_update_7001() {
// first we need to check if this field doesn't exist
if (!field_info_field('my_awsome_field')) {
 // Create the field base.
    $field = array(
      'field_name' => 'my_awsome_field',
      'type' => 'list_boolean',
    );
    field_create_field($field);
// we are going to insert this field in article and page Content Type
$bundle = array('article', 'page');
foreach ($bundles as $bundle) {
      // Create the field instance on the bundle.
      $instance = array(
        'field_name' => 'my_awsome_field',
        'entity_type' => 'node',
        'label' => 'My Awesome Field',
        'bundle' => $bundle,
        'widget' => array(
          'type' => 'options_onoff',
          'settings' => array('display_label' => 1),
          'weight' => 1,
        ),
      );
      field_create_instance($instance);
    }
}
}

That was straight forward. So we have created the field, now we need to insert data for this field to existing node as checked. Remember we need to insert data in revision table as well, if you have revision enable.

foreach (array('data', 'revision') as $table_type) {
  $time_start = microtime(TRUE);
  $table = 'field_' . $table_type . '_field_my_awsome_field';
  $sql = <<<SQL
INSERT INTO $table (
       entity_type, bundle, deleted, entity_id, revision_id, language, delta, my_awsome_field_value
   )
  SELECT
     'node',  type, 0, nid, vid, language, 0, 1 
     FROM node WHERE type IN ('article', 'page')
SQL;
  $result = db_query($sql);
  if ($result->rowCount() > 0) {
    drupal_set_message(t(':func inserted :rows rows into :table in :time seconds.', array(
      ':func' => __FUNCTION__,
      ':time' => (microtime(TRUE) - $time_start),
      ':rows' => $result->rowCount(),
      ':table' => $table,
    )));
  }
}
field_cache_clear();

Thats a lot of code. lets go through line by line

foreach (array('data', 'revision') as $table_type)

this would loop through each type of data table i.e data and revision

 $time_start = microtime(true);

I have used it just to see how long it takes to update x amount of nodes.

$table = 'field_' . $table_type . '_field_my_awsome_field';

generating table name based on type of data table. this is based on drupal naming convention. A field is named as field_xxx_field_yyy where xxx = type of data table (i.e data or revision) yyy = name of the field

 $sql = <<<SQL
     INSERT INTO $table (
       entity_type, ...............

This is combined query where we selecting each row from node table where its type is either article or page and then inserting new field data into it.

 if ($result->rowCount() > 0) {
     drupal_set_message(t(':func inserted :rows rows into :table in :time seconds.', array(
       ':func' => __FUNCTION__,
       ':time' => (microtime(true) - $time_start),
       ':rows' => $result->rowCount(),
       ':table' => $table,
     )));
   }

this is just to calculate the time and number rows updated 

 field_cache_clear();

last bit, clearing the field cache so updated data is available straight after update hook ran.

I was able to update 20k nodes in 3 seconds, which is a lot faster and efficient to update that many nodes.

Complete code as below:

function My_module_update_7001() {
// first we need to check if this field doesn't exist
  if (!field_info_field('my_awsome_field')) {
    // Create the field base.
    $field = array(
      'field_name' => 'my_awsome_field',
      'type' => 'list_boolean',
    );
    field_create_field($field);
// we are going to insert this field in article and page Content Type
    $bundle = array('article', 'page');
    foreach ($bundles as $bundle) {
      // Create the field instance on the bundle.
      $instance = array(
        'field_name' => 'my_awsome_field',
        'entity_type' => 'node',
        'label' => 'My Awesome Field',
        'bundle' => $bundle,
        'widget' => array(
          'type' => 'options_onoff',
          'settings' => array('display_label' => 1),
          'weight' => 1,
        ),
      );
      field_create_instance($instance);
    }
    foreach (array('data', 'revision') as $table_type) {
      $time_start = microtime(TRUE);
      $table = 'field_' . $table_type . '_field_my_awsome_field';
      $sql = <<<SQL
     INSERT INTO $table (
       entity_type, bundle, deleted, entity_id, revision_id, language, delta, my_awsome_field_value
)
  SELECT
     'node',  type, 0, nid, vid, language, 0, 1 
     FROM node WHERE type IN ('article', 'page')
SQL;
      $result = db_query($sql);
      if ($result->rowCount() > 0) {
        drupal_set_message(t(':func inserted :rows rows into :table in :time seconds.', array(
          ':func' => __FUNCTION__,
          ':time' => (microtime(TRUE) - $time_start),
          ':rows' => $result->rowCount(),
          ':table' => $table,
        )));
      }
    }
    field_cache_clear();
  }
}