Custom queries on CCK fields

When we started writing custom code against CCK objects, we took the quick and dirty approach of looking at the underlying database tables manually and figuring out which tables and columns we wanted to query on. It turns out that CCK provides a better way, a way that will mean your code does not break if the field definition changes.

There are two CCK API functions that are key to this:

  •  content_fields() – This function takes a field name as a parameter and returns an array of information about that field. The array contains almost everything, except database information. You can also pass a content type name as a second parameter to only get information for that context.
  • content_database_info() – This function takes a field definition (as returned by content_fields()) and returns an array of database storage information for that field.

So if you want to know how field field_file is stored, you would call:

  • content_database_info(content_fields(‘field_file’));

This will return an array with two keys:

  • ‘table’, which contains the name of the database table where the data is stored.
  • ‘columns’ which is an array of columns in that table related to that field. Depending on the field type there may be one or more entries in this array.

The columns array contains an array for each attribute of the field, keyed on the attribute name. For simple fields there will be just one attribute called ‘value’. For more complex field types you will need to look at the array to find out which attribute you’re interested in. Once you’ve found the attribute, there as yet another array to look at, which contains a few keys but the only one we’re interested in is ‘column’ which contains the name of the database column for that attribute.

Once you have all this information, you can construct your SQL query without hard-coding any CCK tables or columns.

Here’s an example array from a field of type file. This field type has three attributes, so there are three columns to choose from:

array (
‘table’ => ‘content_field_file’,
‘columns’ =>
array (
‘fid’ =>
array (
‘type’ => ‘int’,
‘not null’ => false,
‘views’ => true,
‘column’ => ‘field_file_fid’,
‘list’ =>
array (
‘type’ => ‘int’,
‘size’ => ‘tiny’,
‘not null’ => false,
‘views’ => true,
‘column’ => ‘field_file_list’,
‘data’ =>
array (
‘type’ => ‘text’,
‘serialize’ => true,
‘views’ => true,
‘column’ => ‘field_file_data’,

Iteration 14

We’ve just embarked on Iteration 14 which will focus on the new Channels system  as well as the News Room site.  Iteration 14 will run for three weeks from July 16th through to August 1st.

Work underway this iteration includes:

  • UI and backend work related to the upcoming release of the new McGill Channels system.
  • Adjustments to revision handling in eCal system.
  • An update to our WordPress theme.
  • Data migration related to an upcoming restructuring of the /gps and  /applying sites.
  • A social media book marking block.
  • A redesign of the HR home page to make it appropriately semantic and accessible.

Iteration 13: Update

Iteration 13 ran from June 14th through to July 11th.

It was a four week iteration the focused primarily on addressing a few remaining issues with the Channels system.  The following work was undertaken and completed in I13:

  • UI and backend work related to the upcoming release of the new McGill Channels system including:
    • Date presentation
    • Location presentation
    • Display of channels archives on the new /channels site
    • Improved handling for all day events
  • Bugs and data migration related to the eCal system.
  • A bug with the megamenu system preventing block configuration from working properly.
  • Code review for our parters in Libraries and ISR.
  • Data migration related to an the restructuring of the HR site.
  • Data export for Important Dates

A total of 86 story points worth of work was completed in Iteration 13.

Blog authors are solely responsible for the content of the blogs listed in the directory. Neither the content of these blogs, nor the links to other web sites, are screened, approved, reviewed or endorsed by McGill University. The text and other material on these blogs are the opinion of the specific author and are not statements of advice, opinion, or information of McGill.