Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
By orjantorang on
How do you make a correct reference in a LEFT JOIN between node and content_type_xxx, shall I use .nid or .vid?
Is there a description of Drupal database somewhere?
Thanks,
Örjan
Comments
NID - Node ID VID - Node
NID - Node ID
VID - Node Revision ID (so, version ID)
Install the schema module and take a look at the 'describe' tab, it'll tell you what each db table is for (assuming the developer has entered a description). If you take a look at node and node_revisions you'll see that they're matched together by certain entries. So... node_nid matches node_revisions_nid and node_vid contains the *current* version of the page which you'd match against node_revisions_vid to return the latest node body (and whatever else you may need) - you'd therefore use both to return the current revision of the required node. If you need an example let me know.
Pobster
Correct me if I'm wrong, a
Correct me if I'm wrong, a new revision is created when you editing your content of any type and choose "Create new revision"?
I've used CCK-field to a own node-type and want to create an SQL-statement for Sphinx search engine. It looks like this
SELECT node.nid .... FROM node LEFT JOIN content_type_company ON node.nid = content_type_company.vid (nid?)...I don't think I'm going to use revisions in this index, just the latest information.
If I got this right I should select the highest value of "vid" for each "nid" in my SQL-statement?
/Örjan
Well... You're not wrong,
Well... You're not wrong, but the body of a node isn't stored in node_body any more, so you always have to grab the node revision and match it to return the body from node_revisions regardless of whether you've ever created new revisions or not. Honestly, take a look at the schema module and everything should become clearer.
...and in answer to the last part of what you asked; whilst ... yes... returning the highest version id will return the original or latest body (if there are no new revisions) obviously the correct way to do it is to use the vid drupal has in the node table as this points to the most current revision. Just match everything together and it'll return what you need.
Pobster
Thanks!
The Schema module had some nice features showing descriptions based on information from the install-files, I suppose. But as pobster did mentioned it requires that the programmer has made a correct install-file and that it belongs to a module. In my case with CCK-field there is no description. So we have to ask someone who knows :-)
Thanks again!
Ah I thought you only really
Ah I thought you only really just wanted to understand how it works! (From your first post asking after the 'description'!) Well, what I wrote above still stands regardless of the fact I glazed over that you're enquiring about a CCK field... It's still exactly the same, 'node' holds node information, 'node_revisions' holds node data and 'content_type_***' contains the CCK field data. The nids will all correspond and the vids also will all correspond, the highest version id doesn't necessarily have to be the 'current' one, so be wary of that - best to take whatever is stored in 'node_vid' as your value for this.
Pobster