Use like operator and order by sub query-CodePudding

Introduction

I have a project where we help the IT community to organize better IT meetups (just like meetup.com but only for IT community) called

For the mysql magicians, here is the raw query

select
  `tags`.*,
  (
    select
      count(*)
    from
      `meetups`
        inner join `meetup_has_tags` on `meetups`.`id` = `meetup_has_tags`.`meetup_id`
    where
        `tags`.`id` = `meetup_has_tags`.`tag_id`
  ) as `meetups_count`
from
  `tags`
where
    `title` LIKE 'javascript%'
order by
  `meetups_count` desc
limit
  2 offset 0

Question

The main objective here is to return the most relevant result to the user. He writes javascript and javascript shows up first followed by less "relevant" results. The way I found was to sort by the number of times a tag was used.

Is there a solution where I can do "please, fetch the results that match this query first, then return the most relevant results"?

By "most relevant" results, I simply mean "what the user is looking for". If he writes "javascript" it should return "javascript" followed by "javascript-tools" (because "javascript-tools" was used twice but the user is literally searching for "javascript")

CodePudding user response：

Here is your query:

SELECT * FROM
(SELECT tags.*,COALESCE((SELECT COUNT(*) FROM group_has_tags WHERE tag_id = tags.id),0) AS usage
FROM tags
WHERE title LIKE 'javascript%') AS tmp
ORDER BY tmp.name = 'javascript' DESC,usage DESC

For each matching tag you get the number of times it has been used. Then you first sort by whether the tag matches literally what the user has typed, then by the usage. Of course you will have to parameterize this query but I hope you get the idea.