By _martin on

If I create a new node type using CCK, is there a way to include its fields in DB queries?

Going back to the book review example in my previous post, I know how to use drupa_query to get a list of all nodes of type genre, but how would I write a query to get all the review nodes with a partcular genre (assuming genre is a new field added by CCK)?

⋅ Categories: Drupal 6.x

Comments

WorldFallz’s picture

WorldFallz commented

querying cck fields directly can be quite complicated-- imo the views module is a better way to go.

===
"Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime." - Lao Tzu
"God helps those who help themselves." - Ben Franklin
"Search is your best friend." - Worldfallz

_martin’s picture

_martin commented

I am struggling to find an example of how to link tables together with views. I think I am probably missing something simple. Are there any examples or tutorials which go beyond the very basics?

Eugene Dubois’s picture

Eugene Dubois commented

You should create a view. Then do a preview.

The generated sql will be shown, and you can use that in your modules.

_martin’s picture

_martin commented

That sounds quite promising, I will give it a go.

smanes’s picture

smanes commented

I didn't see your earlier message but perhaps this will get you heading in the right direction. This is for D6, which is the only Drupal database I've worked with.

The node table has a couple of unique IDs: 'nid' and 'vid'. The 'nid' is the node number and the 'vid' is the revision id from the node_revisions table. The node_revisions table contains the latest updates for the node. It's the 'vid' that CCK uses for its pivot key, or what would be an FK in a more... ahem... modern database.

When you create a CCK field it creates a table with a name based on the node type. For instance, if you create a CCK field on a content type of 'feed' it will create a table called "content_type_feed". Its primary key is 'vid'. So if you want to extract the CCK values associated with a node you JOIN the node table's 'vid' with the content_type_XXX table's 'vid'. Then you extract the named CCK field (which you named when you created the field), appending "_value" to it. CCK creates multiple columns in this table for each CCK field on that content type. "_value" is usually the one you're interested in.

For instance, let's say you have a CCK field called "field_geocode" that you attached to a content type of 'feed'. Here's a query that would extract the nid, the node's title and the CCK geocode value:

 SELECT
    node.nid AS nid,
    node.title AS title,
    CT.field_geocode_value AS geocode
 FROM
    node
    LEFT JOIN content_type_feed CT ON (CT.vid = node.vid)
 WHERE
    node.type = 'feed';

YMMV.