3

I'm building a View that lists contacts. The View needs to list all contacts that are not tagged with a certain tag. The problem is if a contact is tagged with another tag, it will still show in the list, because of the LEFT JOIN to the tag table...In this example I'm filtering the list to one contact for simplicity's sake. The contact in question has two tags. One of them is the one I want to filter out. Yet the contact still shows in the list because it has another tag....

I also want to include contacts in the list that have no tags at all, thus the

part of the query ( (civicrm_tag.id IS NULL ) OR (civicrm_tag.id NOT IN ('35')) )

SELECT civicrm_contact.id AS id, civicrm_contact.display_name AS civicrm_contact_display_name
FROM 
{civicrm_contact} civicrm_contact
LEFT JOIN {civicrm_entity_tag} civicrm_entity_tag ON civicrm_contact.id = civicrm_entity_tag.entity_id AND civicrm_entity_tag.entity_table = 'civicrm_contact'
LEFT JOIN {civicrm_tag} civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id
WHERE (( (civicrm_contact.id = '21033') )AND( (civicrm_tag.id IS NULL ) OR (civicrm_tag.id NOT IN  ('35')) ))
LIMIT 10 OFFSET 0
1

I don't know how this would translate to Drupal Views, but here is the SQL to do it:

SELECT 
  contact.id
FROM civicrm_contact contact
LEFT JOIN civicrm_entity_tag et ON
  et.entity_table = 'civicrm_contact' AND 
  et.entity_id = contact.id AND
  et.tag_id = 1 /* ID of the tag you want to exclude */
WHERE et.id IS NULL;

Note that et.tag_id = 1 must be in the ON clause and not in the WHERE clause.

  • ok, I could probably do that alter in a hook_views_query_alter() implementation – jackrabbithanna Jul 17 '17 at 19:50
  • This isn't working for me....I'm still getting results for a contact with the tag I want to exclude, if they have another tag.... – jackrabbithanna Jul 18 '17 at 21:40
  • Here's what the query I have now after some query mods via hook_views_query_alter: SELECT civicrm_contact.id AS id, civicrm_contact.display_name AS civicrm_contact_display_name FROM {civicrm_contact} civicrm_contact LEFT JOIN {civicrm_entity_tag} civicrm_entity_tag ON civicrm_contact.id = civicrm_entity_tag.entity_id AND (civicrm_entity_tag.entity_table = 'civicrm_contact' AND civicrm_entity_tag.entity_id = '35') LEFT JOIN {civicrm_tag} civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id WHERE (( (civicrm_contact.id = '21033') AND (civicrm_tag.id IS NULL ) )) LIMIT 10 OFFSET 0 – jackrabbithanna Jul 18 '17 at 21:41
  • why if et.tag_id = 1 , is 1 excluded from the query? Wouldn't that statement include it? – jackrabbithanna Jul 18 '17 at 23:07
  • 1
    Looks like your query isn't working because it's also joining on civicrm_tag. An exclusion join will only work if you join directly on one table. You can't join on another one which is part of the exclusion. Hard to explain this fully in such a small comment. If you really must join on civicrm_tag, then you'd need to use a sub-query or a temporary table. But I suspect for your purposes you don't need to join on civicrm_tag if you already know the id of the relevant tag. We can chat more on Mattermost (or phone) if you like. – Sean Jul 19 '17 at 3:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.