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 // ... }