CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Discussion (deprecated) »
  • Feature Requests and Suggestions (Moderator: Dave Greenberg) »
  • Advanced search on NOT having tags
Pages: [1]

Author Topic: Advanced search on NOT having tags  (Read 3297 times)

BarisArt

  • Guest
Advanced search on NOT having tags
February 26, 2008, 03:31:26 am
Hi all,

It would be nice to select all users, except those having a specific Tag.

As an example, I use a Blacklist tag, for users who are kicked out of the club.


Thanks in advance!

Kurund Jalmi

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4169
  • Karma: 128
    • CiviCRM
  • CiviCRM version: 4.x, future
  • CMS version: Drupal 7, Joomla 3.x
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Advanced search on NOT having tags
February 26, 2008, 11:43:00 pm
You can use Search Builder for this. Search when tag != (id of Blacklist tag)

kurund
Found this reply helpful? Support CiviCRM

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Advanced search on NOT having tags
February 27, 2008, 12:39:59 am

Though it will not work accurately, since it will only give u all the contacts who are tagged who dont have the blacklist tag. However i suspect most users want the list of all contacts (whether or not they are tagged) who are not "blacklisted"

doing the right thing will require some fancy sql along with some temp table creation and storing of values

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

BarisArt

  • Guest
Re: Advanced search on NOT having tags
February 27, 2008, 01:02:59 am
Why? It isn't that hard..

Example:

SELECT * FROM civicrm_contact
WHERE id NOT IN (
  SELECT contact_id FROM civicrm_entity_tag WHERE tag_id = 8
)

is working for me and very fast!

OR in case of a multiple tag-selector:

SELECT * FROM civicrm_contact
WHERE id NOT IN (
  SELECT DINSTINCT contact_id FROM civicrm_entity_tag WHERE tag_id IN ( 8, 9, 10 )
)

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Advanced search on NOT having tags
February 27, 2008, 01:20:18 am

you are right, in this case the query is not complicated. However we have been burnt in the past with subqueries (check: http://bugs.mysql.com/bug.php?id=2565), so i would probably do some more performance testing for that specific sub query. You might also want to test it as part of the larger query (which has a few left joins in the from clause).

i suspect your db is a few hundred/thousand records. Most of our performance issues are identified when we deal with 10K+ contacts etc

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Discussion (deprecated) »
  • Feature Requests and Suggestions (Moderator: Dave Greenberg) »
  • Advanced search on NOT having tags

This forum was archived on 2017-11-26.