Search code examples
mysqljoomlatags

MySQL: Return only if all values in IN() match a result

Solution

Query

SELECT a.introtext FROM ( SELECT b.content_item_id as id,COUNT(DISTINCT b.tag_id) as rc FROM `#__contentitem_tag_map` AS `b` INNER JOIN `#__tags` AS `c` ON (`b`.`tag_id` = `c`.`id`) WHERE `c`.`alias` IN ("") GROUP BY `b`.`content_item_id`)f INNER JOIN `#__content` AS `a` ON (`a`.`id` = `f`.`id`) INNER JOIN `#__contentitem_tag_map` AS `b` ON (`a`.`id` = `b`.`content_item_id`) INNER JOIN `#__tags` AS `c` ON (`b`.`tag_id` = `c`.`id`) WHERE `c`.`alias` IN ("") AND `a`.`state` = 1 AND `c`.`level` > 0 AND `c`.`published` = 1 AND `a`.`catid` = 27 AND `f`.`rc` = 1 GROUP BY `a`.`id` ORDER BY a.title ASC

Joomla SQL

This is the Joomla SQL inside a custom module

// Create a new query object.
$subQuery = $db->getQuery(true);
$query = $db->getQuery(true);

$tags = array('facebook','html5');
$count = count($tags);
$tags = '"' . implode('","',$tags) . '"';

$subQuery

->select(array('b.content_item_id as id','COUNT(DISTINCT b.tag_id) as rc'))

->from($db->quoteName('#__contentitem_tag_map','b'))

->join('INNER', $db->quoteName('#__tags', 'c') . ' ON (' . $db->quoteName('b.tag_id') . ' = ' . $db->quoteName('c.id') . ')')

->where(($db->quoteName('c.alias') . ' IN ('.$tags.')'))

->group($db->quoteName('b.content_item_id'));

$query

->select(array('a.introtext'))

->from('('.$subQuery.')f')

->join('INNER', $db->quoteName('#__content', 'a') . ' ON (' . $db->quoteName('a.id') . ' = ' . $db->quoteName('f.id') . ')')

->join('INNER', $db->quoteName('#__contentitem_tag_map', 'b') . ' ON (' . $db->quoteName('a.id') . ' = ' . $db->quoteName('b.content_item_id') . ')')

->join('INNER', $db->quoteName('#__tags', 'c') . ' ON (' . $db->quoteName('b.tag_id') . ' = ' . $db->quoteName('c.id') . ')')

->where(($db->quoteName('c.alias') . ' IN ('.$tags.')'), 'AND')

->where(($db->quoteName('f.rc') . ' = ' . $count))

->group($db->quoteName('a.id'))

->order('a.title ASC');

I am using MySQL and the contents of Joomla! 3.8 database.

Tables in use

  • #__content
  • #__contentitem_tag_map
  • #__tags

The Story

I created articles with profiles of Developers. Each Developer can tag him/herself with skills like "jQuery", "html5", etc. Those skills are stored as tags in the #__tags table.

I try to select all developers from the #content table that match the tags I am searching for.

For example I am looking for a dev, who can do 'HTML5' as well as 'JavaScript'.

The results should be empty, because there is a single dev for 'HTML5' & 'CSS3' and another dev for 'CSS3' only in my database.

The ugly truth

Searching for 'HTML5' and 'Javascript' returns 1 result.

The way it should be

Searching for 'HTML5' and 'Javascript' should return 0 results because there is no Dev with both skills.

The Query

SELECT
a.id as id,
a.title,
c.id as tag_id,
c.alias as tag_alias
FROM `#__content` as `a`
INNER JOIN `#__contentitem_tag_map` AS b ON (a.id = b.content_item_id) 
INNER JOIN `#__tags` AS c ON (b.tag_id = c.id)
WHERE c.alias IN ('html5','javascript')

id | title | tag_id | tag_alias
-------------------------------
22 | xyz   |      6 | html5

The Question

What to do? By the way I cannot use stuff like HAVING COUNT(\*) = x; because there will be multiple results with multiple tags.

Thank you for your support! <3

Additional Information

Using HAVING COUNT(\*) = x (where x is the amount of tags used) returns only 1 Developer/Tag and not every Developer with this specific tag.

SQL Fiddle

http://sqlfiddle.com/#!9/f5cb6f/2

Solution

  • I tried to find a solution for your question (I am not sure query can not be better; I don't like very much that I have to repeat two times condition c.alias IN ('html5','javascript') ).

    Perhaps it's not important for you, but this query can be used on other db engine too other than Mysql (eg. MSSQL) as it doesn't use GROUP_CONCAT or "permissive" GROUP BY clause.

    The query seems to work for more values other than two (eg. three alias to check), changing condition for RC too of course.

    In the inner part, it counts the (distinct) value for each tag present in table #__contentitem_tag_map: so in the outer part the query can extract tags having at least two (or the number desired, specified in WHERE condition RC=xx) values present. This permits to "simulate" the sort of "AND condition" you needed. In the outer part query makes other necessary joins to extract columns desired.
    (In the inner part, as you can see, it's not necessary to use table #__content).

    I added following data to your sample data:

    INSERT INTO `#__content` (`id`, `title`) VALUES (29, 'New one');
INSERT INTO `#__contentitem_tag_map` (`content_item_id`, `tag_id`) VALUES (29, 6);
INSERT INTO `#__contentitem_tag_map` (`content_item_id`, `tag_id`) VALUES (29, 1);
INSERT INTO `#__tags` (`id`, `alias`) VALUES (1,'facebook');



SELECT F.ID,
A.title as title,
B.tag_id,
C.alias as tag_alias
FROM (SELECT  b.content_item_id as id,
      COUNT(DISTINCT b.tag_id) AS RC
      FROM `#__contentitem_tag_map` AS b
      INNER JOIN `#__tags` AS c ON b.tag_id = c.id
      WHERE c.alias IN ('html5','facebook')
      GROUP BY b.content_item_id
      ) F
INNER JOIN `#__content` A  ON A.id = F.id
INNER JOIN `#__contentitem_tag_map` B ON a.id = B.content_item_id
INNER JOIN `#__tags` C ON B.tag_id = C.id
WHERE C.alias IN ('html5','facebook')
    AND F.RC=2;
;

    Output:

        ID  title   tag_id  tag_alias
1   29  New one 6   html5
2   29  New one 1   facebook