Tech Blog :: Drupal: Using CCK Fields's Inconsistent Tables in Custom Queries


Aug 2 '10 9:37am
Tags

Drupal: Using CCK Fields's Inconsistent Tables in Custom Queries

Drupal has an inconsistent data structure for CCK fields: if a field is only in one content type, it's stored in a content_type_XX table as a column, but if it's shared across multiple content types, it moves to its own content_field_XX column. Views figures out where fields are located automatically, but in custom SQL queries, this can be a real pain - you can write a query that works one day, then share a field, and the query breaks.

I asked for a solution to this on IRC and was pointed to this post by drewish, Correctly accessing CCK fields in SQL queries. I adapted that method a little to create this helper function:

/**
 * function to get the TABLE or COLUMN for a CCK FIELD
 * method adapted from http://drewish.com/content/2010/06/correctly_accessing_cck_fields_in_sql_queries
 *
 * @param $field_name
 * @param $type 'table' or 'column'
 *
 * to get both table and column, run function twice with each $type
 */
function helper_cck_field_sql($field_name, $type) {
  $field = content_fields($field_name);
  $db_info = content_database_info($field);
 
  if ($type == 'table' && isset($db_info['table'])) {
    return $db_info['table'];
  }
  elseif ($type == 'column' && isset($db_info['columns']['value']['column'])) {
    return $db_info['columns']['value']['column'];
  }
  return NULL;
}

Thanks for this; just going to need this very soon!

The code for 'column' won't work for all field types.
Not all fields use a 'value' column - noderef uses 'nid', userref uses 'vid', filefield uses 'fid'.
Some field types have several columns.

Your helpêr function should take an additional param for the name of the 'field column' for which the db column name is needed.

Er, "inconsistent" is a bit of a stretch here. There are two storage techniques, and it is quite clear when drupal chooses each. Is the sky inconsistent because it is sometimes day and sometimes night?

Anyway, nice function. thanks.

well- i wouldn't call it inconsistent. i'm sure there is a reason for doing it like it's done- i can imagine performance as a reason as most of the fields out there might not be reused. but actually I would have done it differently and would put every field into its own table. would make things easier for custom and module SQL-queries and also cck wouldn't need to change table structures... i think quite some people use views to see what query is generated and copy/modify it into their own code. but if you then reuse a field later (or delete a contenttype that reused a field) your SQL breaks...

Got to agree - I think - with tom.

I've come across this a few times now and it really bugs me. I don't see a valid reason for not sticking the field data in a unique table and referencing them by vid, nid and fid - that's the way the rest of the Drupal database schema seems to work??

Would love some MySQL performance pro to give me an irrefutable reason for the way it's done. It might not bug me so much every time I have to consider this. ;-)

Cheers for the above code, I'll give it ago.

Paul Hudson

This is my tweak to allow multiple things to be returned (since I very rarely want only the column or the table):

function helper_cck_field_sql($field_name, $type='table') {
$field = content_fields($field_name);
$db_info = content_database_info($field);

if (isset($db_info['table']) && isset($db_info['columns']['value']['column'])) {
return array($db_info['table'], $db_info['columns']['value']['column']);
}
return NULL;
}

Called with:

list($column, $table) = helper_cck_field_sql('account_id');

Which of course doesn't work at all. Please disregard me as I slink into the corner.