[support] help with: bypassing Views Taxonomy filter limitation

[sebastian]

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