My situation:
- Multilingual site, using node translation to translate nodes.
- Region is a hierarchical taxonomy: country - region - department.
- "holiday_home" is a content type.
- User should be able to select holiday homes on country/region/department (implemented with an exposed filter with depth = 2).
- I use the march-28 dev version, so earlier fixed issues fixed with more or less same title do IMO not apply.
The generated query looks like:
SELECT node.title AS node_title, node.nid AS nid
FROM
{node} node
WHERE (( (node.language IN ('en')) AND (node.status = '1') AND (node.nid IN (SELECT tn.nid AS nid
FROM
{taxonomy_index} tn
LEFT OUTER JOIN {taxonomy_term_hierarchy} th ON th.tid = tn.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th1 ON th.parent = th1.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th2 ON th1.parent = th2.tid
WHERE ( (tn.tid = 'holiday_home') OR (th1.tid = '4') OR (th2.tid = '4') ))) AND (node.type IN ('holiday_home')) ))
ORDER BY node_title ASC
LIMIT 25 OFFSET 0
Error:
- tn.tid = 'holiday_home': should be the tid of the term as selected by the user. This caters for the depth = 0 case
Improvements:
- the th1 and th2 joins should cater for the depth = 1 resp. depth = 2 cases. So I don't get the join on th, th1 could be joined to tn, this needs some rewriting of the query but that shouldn't be too difficult.
- don't use quotes around integers: would have uncovered this error earlier and more clearly.
Query as should/could be (note that we now compare to the parent field in the th: this allowed me to remove the extra join):
SELECT node.title AS node_title, node.nid AS nid
FROM
{node} node
WHERE (( (node.language IN ('en'))
AND (node.status = '1')
AND (node.nid IN (SELECT tn.nid AS nid
FROM ms_taxonomy_index tn
LEFT OUTER JOIN ms_taxonomy_term_hierarchy th1 ON th1.tid = tn.tid
LEFT OUTER JOIN ms_taxonomy_term_hierarchy th2 ON th2.tid = th1.parent
WHERE ( (tn.tid = 4) OR (th1.parent = 4) OR (th2.parent = 4) )))
AND (node.type IN ('holiday_home')) ))
ORDER BY node_title ASC
LIMIT 25 OFFSET 0
Comments
Comment #1
fietserwinOops, the inner query was copied from a MySql workbench screen, thus with real tables names instead of {}. Query as generated by views should be:
Comment #2
merlinofchaos commentedThe database API does this for us automatically.
An export of your view would help -- it's not clear to me how you're getting a term name into a filter that wants a term id.
Comment #3
merlinofchaos commentedAlso, try with the latest -dev.
Before today, the new taxonomy system caused a proliferation of bad filters and fields. I've reduced these somewhat, which means that the filter you're using may not actually be valid anymore. That could mean you'll need to rebuild this view.
Comment #4
dawehnerUpdate status.
Comment #5
branana commentedThe problem seems to happen if the 'where' added to the view is a SelectQuery obj and if it is not the first entry in the 'where' array.
I was able to get around the issue by placing my depth filters to be the first filters, and all the other types of filters go after them.
Comment #6
merlinofchaos commentedFilters shouldn't be order dependent in any way.
Comment #7
dawehnerAs we figured out in IRC this is a core bug: #1112854: Subqueries use wrong arguments
You can also reproduce the bug so
Comment #8
merlinofchaos commentedNot much we can do if it's a core bug. Follow dereine's link.