[support] help with: bypassing Views Taxonomy filter limitation

[sebastian]

Hello folks,

Just wanted to add that a nested SELECT is not performance optimized.

The "better" SQL query is actually:

SELECT DISTINCT node.nid AS nid, node.title AS node_title, node.vid AS 
node_vid
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
LEFT OUTER JOIN term_node term_node2 ON node.vid = term_node2.vid AND
term_node2.tid = 3
WHERE term_node2.tid IS NULL
AND term_data.vid = 2
GROUP BY node.nid

By using an LEFT OUTER JOIN instead of a WHERE NOT EXISTS (SELECT...


The rule of thumb is: Use JOIN with IS NULL [=inverted selection] 
instead of a derived table.

I didn't come up with this, but someone else schooled me.
:-)

Sincerely,

Sebastian.

On 2011-10-26 11:05 PM, Joel Willers wrote:
> Thanks for posting the solution to this. I'll add it to my collection of solutions to problems I might have in the future.
>
> Joel
>
>
> -----Original Message-----
> From: support-bounces at drupal.org [mailto:support-bounces at drupal.org] On Behalf Of sebastian
> Sent: Wednesday, October 26, 2011 5:13 AM
> To: support at drupal.org
> Subject: Re: [support] help with: bypassing Views Taxonomy filter limitation
>
> OK!
>
> Finally found an answer, here is the correct mysql query to exclude a
> specific TID from the results of your nodes:
>
> SELECT DISTINCT(node.nid) AS nid,
>      node.title AS node_title,
>      node.vid AS node_vid
>    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 NOT EXISTS
>        (SELECT *
>           FROM term_node
>          WHERE term_node.tid = 3
>            AND term_node.nid = node.nid)
>    GROUP BY nid
>
> Obviously this can be modified now to take an argument and the View will
> now deliver correct results.
>
> Phew!
>
> S.
>
> On 2011-10-26 7:05 AM, sebastian wrote:
>> Hi there, not sure why the $query I am seeing has so many wild cards,
>> but here is a far simpler version of the mysql query it makes [reduced
>> to bare essentials]:
>>
>> SELECT DISTINCT(node.nid) AS nid,
>> 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 UPPER(term_data.name) NOT LIKE UPPER('%personal%')
>> GROUP BY nid
>>
>> Again, the problem is that it will still accept [not filter out] a node
>> if it has more than one Taxonomy term and at least one of those terms
>> are not the match [in this case the term "personal"].
>>
>> Is anyone good with sql queries and knows how I would have to change
>> this to eliminate a node if ANY of it's terms are matched instead of ALL?
>>
>> Thank you kindly,
>>
>> Sebastian.
>>
>>
>> On 2011-10-25 6:12 PM, sebastian wrote:
>>> Hello,
>>>
>>> Have a noodle here.
>>>
>>> D6, V2. If I make a View and set the filter to:
>>>
>>> Does NOT contain TERM "personal"
>>>
>>> And then I run the display, it will NOT show a Node that has ONLY one
>>> tag that says "Personal". But if I have more tan one Tag on that node,
>>> then it WILL show the Node, making my filter useless...
>>>
>>> So I wrote a HOOK to pre-process the query:
>>>
>>> function joinTaxonomy_views_query_alter(&$view,&$query) {
>>> if($view->name == 'blog_feed'){
>>> dpm($view);
>>> }
>>> }
>>>
>>> Now I can see that there are values I can change, but I am unfortunetly
>>> not a mySQL query-master.
>>>
>>> The $query that it generates*, which needs adjusting, is:
>>>
>>> SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS nid,
>>> node.title AS node_title,
>>> node.created AS node_created,
>>> node.type AS node_type,
>>> node.vid AS node_vid,
>>> node_revisions.teaser AS node_revisions_teaser,
>>> node_revisions.format AS node_revisions_format,
>>> node.uid AS node_uid
>>> 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
>>> LEFT JOIN {node_revisions} node_revisions ON node.vid =
>>> node_revisions.vid
>>> WHERE (node.status<>  0) AND (node.type in ('%s')) AND
>>> (UPPER(term_data.name) NOT LIKE UPPER('%%%s%%'))
>>> GROUP BY nid
>>> ORDER BY node_created DESC
>>> ) count_alias
>>>
>>> Can anyone tell me what the query SHOULD be so that it excludes any node
>>> which has my defined Term-name and not just nodes that ONLY have my
>>> designated rejected-term filter?
>>>
>>> If it is helpful I can give a full-dump of the $views variable here, but
>>> trying not to kill you with detail.
>>>
>>> If a TID is easier, I know the TID and it won't change very often, if
>>> ever, so I could hard code it to a number, if that's helpful...
>>>
>>> Thank you so much for your help!
>>>
>>> Sincerely,
>>>
>>> S.
>>>
>>> * $query taken from:
>>> views.inc
>>> /**
>>> * Execute the view's query.
>>> */
>>> function execute($display_id = NULL) {
>>> // ...
>>> $query
>>> // ...
>>> }