Help with a SQL query

http://drupal.org/files/man_mod.zip.txt

You'll have to remove the .txt extension.

- Robert Douglass

-----
My Drupal book: Building Online Communities with Drupal, phpBB and WordPress

Seems like people are wondering why I am using a data model that looks like this. The data is filling a role that the Drupal taxonomy system cannot handle, namely, the case where a hierarchical vocabulary grows to have tens of thousands of terms. In such a scenario, the position of the term in the hierarchy is hard to calculate using Drupal's model because the term_hierarchy table only maintains a parent-child relationship and there is no way to easily know how many ancestors a term has:

mysql> select * from term_hierarchy;
+-----+--------+
| tid | parent |
+-----+--------+
|  21 |      0 |
|  22 |     21 |
|  23 |     21 |
|  24 |     21 |
+-----+--------+

The result is that to get all terms with a depth of 2, you have to select the entire table, loop over it a couple times, build the hierarchy in PHP and then return the PHP array with those terms that end up having the right number of parents. We call this taxonomy_build_tree. That explains the 'realm' field in the mms_node table from my original post.

But more efficiency was desired in my design. In the application that this is designed for, the query "Get all models with parent manufacturer = X" is very common. To express that with Drupal taxonomy, one is dependent on the term_data table:

mysql> select * from term_data;
+-----+-----+-------------+-------------+--------+
| tid | vid | name        | description | weight |
+-----+-----+-------------+-------------+--------+
|  21 |   1 | Excellent   |             |      0 |
|  22 |   1 | Fair        |             |      0 |
|  23 |   1 | Outstanding |             |      0 |
|  24 |   1 | Poor        |             |      0 |
+-----+-----+-------------+-------------+--------+

The query that expresses "Get all models with parent manufacturer = X" is this:

SELECT * FROM term_data td INNER JOIN term_hierarchy th ON th.tid = td.tid where th.parent = %d and td.vid = 1;

It's not a horrible query, but with it you can guarantee neither that the results are at the "model" level of the hierarchy nor that the parents are at the "manufacturer" level. You are dependent on the people using the system to never make "General Motors" a child belonging to "Ford". To be sure at the query level that you are getting only terms that are really at the correct depth you have to add the 'realm' (or I could have called it 'depth') field to the term_hierarchy table, and then it would look like this:

SELECT * FROM term_data td 
INNER JOIN term_hierarchy th ON th.tid = td.tid 
INNER JOIN term_data td2 ON th.parent = td2.tid
INNER JOIN term_hierarchy th2 ON td2.tid = th2.tid
WHERE th.parent = %d 
AND th.realm = 2
AND th2.realm = 1
AND td.vid = 1;

My data model it reduces to this:

SELECT id, name FROM model WHERE man_id = %d;

So now at least you can see the motivation for such a schema.

- Robert Douglass

-----
My Drupal book: Building Online Communities with Drupal, phpBB and WordPress