Query to find topics depending on the tag

Question

I want a search functionality in my application for the data like following

topic_id   tag
1          cricket
1          football
2          football
2          basketball
3          cricket
3          basketball
4          chess
4          basketball

Now when i search for term cricket AND football o/p should be

 topic_id
    1

and when i search for term cricket OR football o/p should be

 topic_id
    1
    2
    3

i try something like following

FOR AND

  select topic_id from table_name where tag like "%cricket%" and topic_id in (select topic_id from table_name where tag like "%football%")

FOR OR

 select topic_id from table_name where tag like "%cricket%" OR tag like "%football%"

My problem is when user search for the cricket AND football AND basketball AND chess my Query becomes very pathetic

is there any simple solution for this. I also tried for GROUP_CONCAT but in vain

Answer 1

 SELECT TopicId
 FROM Table
 WHERE Tag IN ('cricket', 'football', 'basketball', 'chess')
 GROUP By TopicId
 HAVING Count(*) = 4

  4 is magic number - its a length of your AND list.

 FOR cricket AND football

 it will be 2:

 SELECT TopicId
 FROM Table
 WHERE Tag IN ('cricket', 'football')
 GROUP By TopicId
 HAVING Count(*) = 2

 if you want use 'like' statement:

 SELECT TopicId
 FROM Table
 WHERE Tag IN (SELECT distinct Tag from Table Where Tag like '...'
                OR Tag like '...'
                OR Tag like '...'
                OR Tag like '...'
              )
 GROUP By TopicId
 HAVING Count(*) = (SELECT COUNT(distinct Tag) from Table 
                    Where Tag like '...'
                       OR Tag like '...' 
                       OR Tag like '...'
                       OR Tag like '...'
                   )

UPDATE:

This task can be easy solved with RDBMS which support all sets operations: UNION, INTERSECT and EXCEPT (or MINUS)

Then any conditions like:

1. (Tag1 AND Tag2) OR Tag3 NOT Tag4
2. Tag1 OR Tag2
3. Tag1 AND Tag2 And Tag3
4. (Tag1 AND Tag2) OR (Tag3 AND Tag4)

can be easily transformed into:

1. (Select * ... Where Tag = Tag1
    INTERSECT
    Select * ... Where Tag = Tag2
   )
   UNION
   (Select * ... Where Tag = Tag3)
   EXCEPT
   (Select * ... Where Tag = Tag4)

2. Select * ... Where Tag = Tag1
   UNION
   Select * ... Where Tag = Tag2

3. Select * ... Where Tag = Tag1
   INTERSECT
   Select * ... Where Tag = Tag2
   INTERSECT
   Select * ... Where Tag = Tag3

 4.(Select * ... Where Tag = Tag1
    INTERSECT
    Select * ... Where Tag = Tag2
   )
   UNION
   (Select * ... Where Tag = Tag1
    INTERSECT
    Select * ... Where Tag = Tag2
   )

The real problem that MYSQL does not support INTERSECT, which should be emulated as shown above. Second problem is respecting brackets and operator precedences.

So possible solution without using brackets in expressions:

1. Collect all tags which joined by AND conditions and build query as first example in answer.

2. Add all tags which joined OR condition (can be used IN or UNION) and by using UNION combine result.

Another approach possible only if you have tag quantity less 64. Then each tag will have own bit (You will need add bigint field 'tags' into topics table where will be represented tags in binary format) and by using mysql bit operations create query.

Big disadvantage that this solution limited only for 64 tags.

Answer 2

You need to do a self join

select distinct topic_id from 
table_name as t1
join
table_name as t2 
on 
t1.topic_id = t2.topic_id
and
t1.tag = "cricket"
and
t2.tag = "football"