Re: [support] help with: bypassing Views Taxonomy filter limitation - support

28 Oct 2011

      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@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of sebastian
Sent: Wednesday, October 26, 2011 5:13 AM
To: support@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
// ...
}