I’m working on understanding how Drupal’s taxonomy schema can be combined in a SELECT query and I’m not familiar with the relation, and therefore can’t find specific information. Its simple enough to illustrate as you can see below. I’ve also tried my hand at the logic that would inform the MySQL commands as pseudo code.
The taxonomy schema has two tables. The first table contains terms and weight-based order (lowest to highest) values. The second table specifies the parent-child relationships of the first table:
table_a table_b on table_a.ID
+-----+------+-----+...+-----+
| ID | TRM | WT |...| P |
+-----+------+-----+...+-----+
|1 | 3 | [9] |...| [0] |
|2 | 1.1 | [0] |...| [9] |
|3 | 2 | [8] |...| [0] |
|4 | 2.1 | [0] |...| [3] |
|5 | 2.2 | [1] |...| [3] |
|9 | 1 | [5] |...| [0] |
+-----+------+-----+...+-----+
table_a = term table,
table_b = hierarchy table
TRM= TERM, WT= WEIGHT, P=PARENT
Logically, to get the first item we need to find the record with a P=0 (meaning it has no parent, and in this scheme is itself a parent) AND its weight value should be the lowest (meaning its the first item). Something like this in pseudo code,
table_a.ID=table_b.P=0 AND table_a.WT=[lowest value] //e.g. P=0, WT=5, Therefore ID=9;
The resulting ID then is the parent ID to find the children records,
table_a.ID=table_b.P AND table_a.WT[lowest value] //e.g. P=9, WT=0, Therefore ID=2;
followed by,
table_a.ID=table_b.P AND table_a.WT=[lowest value] //e.g. P=2, WT=0, Therefore ID=none;
but what I want is the same parent, with the weight incremented,
P=9, WT=1, Therefore ID=none;
And it seems to ziz-zag like this back and forth between the two tables. What I quickly realize is there is a tendency, where the last ID is used to find its siblings, to look for the children’s children. But this is unnecessary, because this hierarchy is only one level deep.
The specific question is simply, what’s the MySQL syntax that joins these two tables in order: 1, 1.1, 2, 2.1, 2.2, 3. And more generally, is there a technical term to describe the use of two tables in this way?