Timeline for Query to get a list of contacts not tagged with a tag, but the user has multiple other tags
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 19, 2017 at 3:51 | comment | added | Sean |
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.
|
|
| Jul 19, 2017 at 3:48 | comment | added | Sean |
et.tag_id = 1 is to specify which tag you want to operate on. Looks like you'd set this to 35 in your example.
|
|
| Jul 18, 2017 at 23:07 | comment | added | jackrabbithanna | why if et.tag_id = 1 , is 1 excluded from the query? Wouldn't that statement include it? | |
| Jul 18, 2017 at 21:41 | comment | added | jackrabbithanna | 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 | |
| Jul 18, 2017 at 21:40 | comment | added | jackrabbithanna | 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.... | |
| Jul 17, 2017 at 19:50 | comment | added | jackrabbithanna | ok, I could probably do that alter in a hook_views_query_alter() implementation | |
| Jul 17, 2017 at 19:47 | history | answered | Sean | CC BY-SA 3.0 |