2

I have this custom module that shows all contacts based on some tags. I can add one or more tags in a field in the module

What I want is to add the option in the module to show:

  1. all contacts that have ALL tags as specified in the module

or

  1. all contacts that have one or more tags as specified in the module

The code I do have just does the latter. How can I add the possibility for option 1?

The code I do have is:

public static function getPeopleByTags($params)
{
  //echo "<pre>";
  $tags = $params->get("tags");

  $model = JModelLegacy::getInstance('Field', 'FieldsModel', array('ignore_request' => true)); //load fields model

  //get people info from database
  $db = JFactory::getDbo();
  $query = $db->getQuery(true);
  $query->select('id,catid,name,alias,image,email_to,published,user_id,tags.tag_id');
  //$query->select('*');

  $query->from($db->quoteName('#__contact_details').'AS contacts');
  $query->join('right','#__contentitem_tag_map AS tags ON `tags`.`content_item_id` = `contacts`.`id`');
  $query->where($db->quoteName("published") ." = 1  AND `tags`.`type_id` = 2 AND `tags`.`tag_id` IN (".implode(',',$tags).") ");
  $query->group('`contacts`.`id`');
  $db->setQuery($query);
  $people = $db->loadObjectList();

  return $people;

}

Joomla 3.9.0

  • Hi Mickmackusa, Thank you for your additional questions. I'll try to expain a bit more: – Clements Radenborg Dec 2 '18 at 12:14
  • Hi Mickmackusa, Thank you for your additional questions. I'll try to expain a bit more: I have a number of contacts with one or more tags, like "SME", "Education", "Coach", "HR specialist". What I would like is that when I enter for example the tags "HR specialist" and "SME" in the option field in the module settings and set the option to "any" that the query will return all contacts that have one or more of the tags. When I set the option to all, the query shoud return just those contacts that have all tags. Hope that helps. – Clements Radenborg Dec 2 '18 at 12:20
  • The query now is: SELECT id,catid,name,alias,image,email_to,published,user_id,tags.tag_id FROM #__contact_detailsAS contacts RIGHT JOIN #__contentitem_tag_map AS tags ON tags.content_item_id = contacts.id WHERE published = 1 AND tags.type_id = 2 AND tags.tag_id IN (45,186) GROUP BY contacts.id I want to change it in showing all contacts with either tag 45 OR 186. – Clements Radenborg Dec 2 '18 at 13:52
3

Tags - Similar module uses HAVING clause to achieve this.

$query->having('COUNT(' . $db->quoteName('tags.tag_id') . ') = ' . count($tags));

But I'm not sure if this is the best possible solution. It could be rather slow because WHERE IN selects all rows matching any tag and HAVING filters only after the rows have been selected.

  • Thank you for your response, but this would only show all contacts that have exactly the tags I give as parameter. The case is that the contacts have more tags than the onces I give as a parameter. I would like to select all contacts that have at least the onces given in the parameter field in the module. – Clements Radenborg Dec 2 '18 at 16:35
  • I can't confirm this. If I search by Tag 1 and Tag 2, articles with Tag 1, Tag 2 and Tag 3 are returned as expected. To be clear, you have to add this clause to your existing query without changing anything else. – Sharky Dec 2 '18 at 17:31
  • Okay, Ill try this tonight. Thanks! – Clements Radenborg Dec 3 '18 at 8:00
  • You both deserve an upvote for your solutions on this question! – Zollie Dec 3 '18 at 16:35
1

I took the time to set up my own test data and Sharky is correct (+1 to Sharky) about implementing a HAVING clause.

$tag_ids = $params->get("tags");
$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select("id, catid, name, alias, image, email_to, published, user_id")
    ->select(" GROUP_CONCAT(tags.tag_id) AS tag_ids")
    ->from("#__contact_details AS contacts")
    ->innerJoin("#__contentitem_tag_map AS tags ON tags.content_item_id = contacts.id")
    ->where("published = 1")
    ->where("type_id = 2")
    ->where("tag_id IN (" . implode(',', $tag_ids) . ")")
    ->group("contacts.id")
    ->having("COUNT(*) = " . sizeof($tag_ids));

echo $query->dump();
$db->setQuery($query);
echo "<pre>";
var_export($db->loadObjectList());

Output:

SELECT id, catid, name, alias, image, email_to, published, user_id, GROUP_CONCAT(tags.tag_id) AS tag_ids
FROM zyxwv_contact_details AS contacts
INNER JOIN zyxwv_contentitem_tag_map AS tags ON tags.content_item_id = contacts.id
WHERE published = 1 AND type_id = 2 AND tag_id IN (9,10)
GROUP BY contacts.id
HAVING COUNT(*) = 2
array (
  0 => 
  stdClass::__set_state(array(
     'id' => '12',
     'catid' => '21',
     'name' => 'Sharky',
     'alias' => 'sharky',
     'image' => 'images/sharky.jpg',
     'email_to' => '',
     'published' => '1',
     'user_id' => '0',
     'tag_ids' => '9,10',
  )),
  1 => 
  stdClass::__set_state(array(
     'id' => '82',
     'catid' => '21',
     'name' => 'mickmackusa',
     'alias' => 'mickmackusa',
     'image' => '',
     'email_to' => '',
     'published' => '1',
     'user_id' => '0',
     'tag_ids' => '9,10',
  )),
)
  • To show all of the tag_ids in the result set, use GROUP_CONCAT() in the SELECT clause.
  • I prefer the logic of using INNER JOIN versus RIGHT JOIN, but I don't know how they compare in terms of performance.
  • The HAVING clause may use COUNT(*) to achieve the same result as feeding the tag_id column to it.

Changing the number that follows COUNT() will dictate how many qualifying tags are required for a contact/user to be included in the result set. For instance, if you search for 3 different tags, but only require 2, you will get all rows where contacts/users have 2 out of 3 qualifying tags.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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