CODEWITHSUNDEEP
phpmysql
Bruno Francisco
Bruno Francisco

Reputation: 4218

Use like operator and order by sub query

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 https://codotto.com/

We are developing a new feature where we would like to show the number of usages of certain tags. The algorithm should work just like Stackoverflow's one. You write javascript and you get a list with the tags that match your query sorted by the most used ones.

I'm currently using Laravel but I will post the raw query so that it's easier for mysql wizards to help me out if possible :)

Problem

I have the following tables

tags table

id  name
1   javascript
2   javascript-tools
3   javascript-security

group_has_tags table

group_id  tag_id
1         2
2         2
2         3

We have the tags javascript-tools being used two times, javascript-security is used one while javascript is not used at all.

Now, if a user search for javascript, he should get first javascript (because it is a direct match) followed by the rest of the tags sorted by their usage.

In Laravel this is the code that I have (simplified ofc)

$tags = Tag::withCount('groups')
  ->orderBy('groups_count', 'DESC')
  ->where('name', 'LIKE', 'javascript%')
  ->get(2);

The problem is that since I only return back 2 results, javascript is not being included in the results, even tho it's what the user literally wrote

enter image description here

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")

Upvotes: 0

Views: 30

Answers (1)

IVO GELOV
IVO GELOV

Reputation: 14259

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.

Upvotes: 1

Related Questions