By Mark_J on

hey there,

I have been scanning drupal's database and frankly, it's quite interesting the way it's structured but I am having trouble figuring something out.

How does Drupal know whether "story1" is part of "Category X" or "Category Y"?

Here is how Drupal's node_revisions table is structured:

nid | vid | uid | title | body | teaser | log | timestamp | format

Now let's say we have five entries in it: Story1, Story2, Story3, Story4 and Story5.

Now let's also say we have two categories: "World" and "Sport"

Story1, Story2 and Story3 are part of category "World" and Story4 and Story5 are part of category "Sport"

The node_revisions table does not not have a field that lists each story's associated category. For example, I want to build an SQL query that will return all records from node_revisions but in category "world". I just need the SQL syntax. Normally, one would think the syntax would go something like:

SELECT * FROM node_revisions where CATEGORY = 'WORLD'

But I don't have a field called category and this is exactly what I want. I want to return entries via SQL from the category I specify.

Drupal 6.x

⋅ Categories: Drupal 6.x

Comments

cog.rusty’s picture

cog.rusty commented

I am not sure what is the "category" that you are referring to. Are you using the category module?

I can tell you how it works with the core taxonomy module and its terms (its "categories"). A node can be tagged with many different terms from different vocabularies, so the terms can't all be in the node. There is a term_node table which holds (nid, vid, tid) associations, a term_data table which holds the term's name, and all these tables must be combined ("joined") to get something meaningful:

SELECT node.*
FROM node
JOIN term_node ON term_node.nid = node.nid AND term_node.vid = node.vid
JOIN term_data ON term_data.tid = term_node.tid
WHERE term_data.name = "dogs"

If you are using the category module, I think it also has a category_node table to associate node revision IDs with category IDs (cid), but stores the category names in category nodes. You can find more details about that module in the category_schema() function in http://drupalcode.org/viewvc/drupal/contributions/modules/category/categ...

Mark_J’s picture

Mark_J commented

Hi cog.rusty,

Thanks for that. I will try out what you suggested as it sounds as what I need to achieve. I will update this post again.

By the way, no I am not using the category module. I just use the taxonomy/vocabulary terms.

Basically I want to return all stories in for example the "sports" term, or "music" term in display their titles and teasers inside the body of the node. There is a module that does something similar called "insert_block" but I need more control hence for taking matters manually. I will try out your SQL code above.

cog.rusty’s picture

cog.rusty commented

API function often are a better way. The following, for example, could deal with many complicated cases without requiring much knowledge of the database schema:

http://api.drupal.org/api/function/taxonomy_select_nodes/6

Direct SQL queries are fine for learning how to handle the database schema, and in cases that there is no suitable/convenient/simple enough API function.

Mark_J’s picture

Mark_J commented

Thank you very much cog.rusty, the code above was a good starting point. I've made some refinements :)

Check this out :)

SELECT teaser,title
FROM node_revisions
JOIN term_node ON term_node.nid = node_revisions.nid
AND term_node.vid = node_revisions.vid
JOIN term_data ON term_data.tid = term_node.tid
WHERE term_data.name = "sports"

I will create my own PHP wrapper function. I am also checking out the link above.

cog.rusty’s picture

cog.rusty commented

That is fine. Maybe only one problem. If a node has many revisions, all the revisions will be listed if they have the "sports" tag.

That is why I used the "node" table, which has unique nids with only the latest revision's vid.

aliak’s picture

aliak commented

thanks also cog.rusty, this link to the api call helped my issue too (different but similar to reported here)