[support] help with: bypassing Views Taxonomy filter limitation

[sebastian]

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