I may have come across an issue related to the sub-select performed by the Views module when (1) using a hierarchal, multiple select vocabulary and (2) a term argument with depth is enabled. The sub-select is grabbing a list of node IDs when it should be selecting a list of term IDs. Here is an example to illustrate my point:
Food Vocabulary
* Fruit (ID: 3)
* Apple (ID: 7)
* Sour (ID: 6)
* Sweet (ID: 5)
Food Content Type and Example Node Attached to Terms
Food
Sour Sweet Fruit
|
| Apple
| |
--------|---------
|
Granny Smith (of Food Content Type)
Now lets create a view to do the following: (1) select the node title and term name fields (causing JOINs with the term_hierachy and term_node tables) and (2) add a Term ID (with depth) argument.
If I select the sour food (ID: 6), this SQL and output is generated:
SELECT node.nid AS nid,
term_data.name AS term_data_name,
term_data.vid AS term_data_vid,
term_data.tid AS term_data_tid,
node.title AS node_title
FROM node node
LEFT JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
WHERE (node.type in ('food')) AND (node.vid IN (
SELECT tn.vid FROM term_node tn
LEFT JOIN term_hierarchy th ON th.tid = tn.tid
LEFT JOIN term_hierarchy th1 ON th.parent = th1.tid
WHERE tn.tid = 6
OR th1.tid = 6
))
+-----+----------------+---------------+---------------+--------------+
| nid | term_data_name | term_data_vid | term_data_tid | node_title |
+-----+----------------+---------------+---------------+--------------+
| 10 | Apple | 2 | 7 | Granny Smith |
| 10 | Sour | 2 | 6 | Granny Smith |
+-----+----------------+---------------+---------------+--------------+
The sub-select is returning a single row (nid = 10), but the LEFT JOINs on the term* tables are then generating duplicate results. I believe the sub-select should be selecting term_node.tid instead (like the normal Term ID --without depth-- argument generates). Like this:
SELECT node.nid AS nid,
term_data.name AS term_data_name,
term_data.vid AS term_data_vid,
term_data.tid AS term_data_tid,
node.title AS node_title
FROM node node
LEFT JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
WHERE (node.type in ('food')) AND (term_node.tid IN (
SELECT tn.tid FROM term_node tn
LEFT JOIN term_hierarchy th ON th.tid = tn.tid
LEFT JOIN term_hierarchy th1 ON th.parent = th1.tid
WHERE tn.tid = 6
OR th1.tid = 6
))
+-----+----------------+---------------+---------------+--------------+
| nid | term_data_name | term_data_vid | term_data_tid | node_title |
+-----+----------------+---------------+---------------+--------------+
| 10 | Sour | 2 | 6 | Granny Smith |
+-----+----------------+---------------+---------------+--------------+
I attached a diff of changes to views_handler_argument_term_node_tid_depth.inc
| Comment | File | Size | Author |
|---|---|---|---|
| #4 | views_arg_taxo_depth.patch | 3.21 KB | gerhard killesreiter |
| views-taxonomy-depth.diff | 654 bytes | copelco |
Comments
Comment #1
merlinofchaos commentedYour patch cannot be used; it refers directly to a table name in an add_where clause, which is not relationship safe. Views aliases all tables and tables must *only* be referred to by aliases or they will not work when fields are added via relationships.
Comment #2
copelco commentedYeah, I thought my patch might not be up to par, but I'm new to the views module / drupal programming. I figured someone who understand the code would have a better idea. Does that mean it's not a bug though? I don't understand why it's marked as won't fix when it has a bad patch. I believe it's still a bug with Views.
Comment #3
merlinofchaos commentedWhoops, I set the wrong status.
Comment #4
gerhard killesreiter commentedI've got a patch which tries to solve this problem.
It is tested for depth = 1.
Comment #5
summit commentedHi, Will this patch make it possible to have ONLY nodes on a certain term depth? Instead of having from depth down (+ numeric) or from depth up (- numeric)?
Thanks for your answer in advance!
Greetings, Martijn
Comment #6
merlinofchaos commentedWell, seems to work. Let's cross fingers.
Comment #7
merlinofchaos commentedI had to revert this patch. See http://drupal.org/node/583934 and http://drupal.org/node/578808
Comment #8
esmerel commentedGeneral closing of issues with no activity for 6 months
Comment #9
summit commentedHi, I see this patch is reverted but how is it now possible to build a view which is depended on term depth please?
Sorry if wrong question for this issue.
greetings, Martijn
Comment #10
uplex_slink commented