This seems to be a common issue, but I haven't been able to resolve it
yet, so I thought I'd see if anyone has any suggestions. Drupal has a
table that stores all revisions to a given node and I have been unable
to pull individual records out without also grabbing all revisions.
I'd like to pull out the most recent revision for every given node of
type 'blog' in a given category.

Here's the code I'm using:

select distinct(n.nid), n.title,r.teaser,u.name,n.type,t.tid from node
n INNER JOIN term_node t using n.nid = t.nid INNER JOIN term_data ON
t.tid INNER JOIN users u ON n.uid = u.uid INNER JOIN node_revisions r
ON r.nid = n.nid where t.tid = 1 and n.type="blog";

And the relevent tables.

describe node;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| nid | int(10) unsigned | | PRI | NULL | auto_increment |
| vid | int(10) unsigned | | PRI | 0 | |
| type | varchar(32) | | MUL | | |
| title | varchar(128) | | MUL | | |
| uid | int(10) | | MUL | 0 | |
| status | int(4) | | MUL | 1 | |
| created | int(11) | | MUL | 0 | |
| changed | int(11) | | MUL | 0 | |
| comment | int(2) | | | 0 | |
| promote | int(2) | | MUL | 0 | |
| moderate | int(2) | | MUL | 0 | |
| sticky | int(2) | | | 0 | |
+----------+------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

mysql> describe node_revisions;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| nid | int(10) unsigned | | MUL | 0 | |
| vid | int(10) unsigned | | PRI | 0 | |
| uid | int(10) | | MUL | 0 | |
| title | varchar(128) | | | | |
| body | longtext | | | | |
| teaser | longtext | | | | |
| log | longtext | | | | |
| timestamp | int(11) | | | 0 | |
| format | int(4) | | | 0 | |
+-----------+------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql> describe term_node;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| nid | int(10) unsigned | | PRI | 0 | |
| tid | int(10) unsigned | | PRI | 0 | |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe term_data;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+-------------+------------------+------+-----+---------+----------------+
| tid | int(10) unsigned | | PRI | NULL |
auto_increment |
| vid | int(10) unsigned | | MUL | 0 |
|
| name | varchar(255) | | | |
|
| description | longtext | YES | | NULL |
|
| weight | tinyint(4) | | | 0 |
|
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)



Any suggestions?

salvador wrote:

> This seems to be a common issue, but I haven't been able to resolve it
> yet, so I thought I'd see if anyone has any suggestions. Drupal has a
> table that stores all revisions to a given node and I have been unable
> to pull individual records out without also grabbing all revisions.
> I'd like to pull out the most recent revision for every given node of
> type 'blog' in a given category.
>
> Here's the code I'm using:
>
> select distinct(n.nid), n.title,r.teaser,u.name,n.type,t.tid from node
> n INNER JOIN term_node t using n.nid = t.nid INNER JOIN term_data ON
> t.tid INNER JOIN users u ON n.uid = u.uid INNER JOIN node_revisions r
> ON r.nid = n.nid where t.tid = 1 and n.type="blog";
>
> And the relevent tables.
>
> describe node;
> +----------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+------------------+------+-----+---------+----------------+
> | nid | int(10) unsigned | | PRI | NULL | auto_increment |
> | vid | int(10) unsigned | | PRI | 0 | |
> | type | varchar(32) | | MUL | | |
> | title | varchar(128) | | MUL | | |
> | uid | int(10) | | MUL | 0 | |
> | status | int(4) | | MUL | 1 | |
> | created | int(11) | | MUL | 0 | |
> | changed | int(11) | | MUL | 0 | |
> | comment | int(2) | | | 0 | |
> | promote | int(2) | | MUL | 0 | |
> | moderate | int(2) | | MUL | 0 | |
> | sticky | int(2) | | | 0 | |
> +----------+------------------+------+-----+---------+----------------+
> 12 rows in set (0.00 sec)
>
> mysql> describe node_revisions;
> +-----------+------------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+------------------+------+-----+---------+-------+
> | nid | int(10) unsigned | | MUL | 0 | |
> | vid | int(10) unsigned | | PRI | 0 | |
> | uid | int(10) | | MUL | 0 | |
> | title | varchar(128) | | | | |
> | body | longtext | | | | |
> | teaser | longtext | | | | |
> | log | longtext | | | | |
> | timestamp | int(11) | | | 0 | |
> | format | int(4) | | | 0 | |
> +-----------+------------------+------+-----+---------+-------+
> 9 rows in set (0.00 sec)
>
> mysql> describe term_node;
> +-------+------------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+------------------+------+-----+---------+-------+
> | nid | int(10) unsigned | | PRI | 0 | |
> | tid | int(10) unsigned | | PRI | 0 | |
> +-------+------------------+------+-----+---------+-------+
> 2 rows in set (0.00 sec)
>
> mysql> describe term_data;
> +-------------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra
> |
> +-------------+------------------+------+-----+---------+----------------+
> | tid | int(10) unsigned | | PRI | NULL |
> auto_increment |
> | vid | int(10) unsigned | | MUL | 0 |
> |
> | name | varchar(255) | | | |
> |
> | description | longtext | YES | | NULL |
> |
> | weight | tinyint(4) | | | 0 |
> |
> +-------------+------------------+------+-----+---------+----------------+
> 5 rows in set (0.00 sec)
>
>
>
> Any suggestions?

You need to use the "strawberry query". Search this forum for
"strawberry query" (include the quotes).

What in the above tables indicates the the recentness (made up word) of
the revision? If you let me know that, I'll try to construct the query
for you.