CODEWITHSUNDEEP
sqlrelational-division
Gray Adams
Gray Adams

Reputation: 4117

Search for data that matches every single tag (using the LIKE operator)

For the past two days I've been doing my best to put together a query that will pull data based on inputted tags. The purpose is for an autocomplete field, where the words the user inputs are split into tags. I really need to use the LIKE operator because the whole purpose of autocomplete is that the user does not need to write out full words

CREATE TABLE `movies` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tags` (
  `tag` varchar(50) NOT NULL DEFAULT '',
  `mid` int(10) unsigned NOT NULL,
  KEY `mid` (`mid`),
  KEY `alphabetizer` (`tag`),
  CONSTRAINT `tags_ibfk_1` FOREIGN KEY (`mid`) REFERENCES `movies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Current query:

SELECT *
    FROM movies ,
    JOIN tags t ON m.id = t.mid
   WHERE t.tag LIKE 'Dawn%' OR t.tag LIKE 'of%'
GROUP BY m.id
  HAVING COUNT(DISTINCT m.tag) = 2

EDIT: The issue is that, as more tags are added, the vaguer the results get. This is the opposite effect.

Upvotes: 0

Views: 54

Answers (2)

Rahul
Rahul

Reputation: 77886

What if you do something like this

select mid, 
group_concat(tag separator ' ') as fulltag
from tags
group by mid

the above query will get the entire tag list. say for movie_id 1 if tags are dawn,of,planet then query result would be

1  dawn of planet

Then do a join like below. Also, if I consider that user enters tags as dawn and of together then before passing it to query, you can join them to make dawn of as a single string and then use a LIKE operator against it. That way you don't need to plumb multiple LIKE together.

So essentially, if user enters dawn as tag say like 'dawn%'. if user enters dawn , of and planet as tag then join them to make dawn of planet and say like 'dawn of planet%'.

This joining the tags you can perform in your app code and then pass that as parameter to your query.

select m.name,
tab.fulltag
from movies m
join
(
select mid, 
group_concat(separator ' ') as fulltag
from tags
group by mid
) tab on m.id = tab.mid
where tab.tag LIKE 'Dawn Of%'

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

SELECT m.id, group_concat(t.tag separator ', ') as tags
  FROM movies
  JOIN tags t
    ON m.id = t.mid
 GROUP BY m.id
HAVING group_concat(t.tag) like '%DAWN%'
   and group_concat(t.tag) like '%OF%'

NOTE: Are the tags uppercase or lowercase, or mixed? In the above answer I assume they are all uppercase. You can use the UPPER or LOWER functions if the tags are not consistent, but they should be consistently upper or lowercase.

In the above query I used group_concat to show all tags on one row for each ID. That is a MYSQL function that will only work in MYSQL (you didn't mention what database you're using). If postgresql you would use string_agg, if Oracle you would use LISTAGG.

Upvotes: 1

Related Questions