CODEWITHSUNDEEP
mysqlsqlselectsql-order-by
Superangel
Superangel

Reputation: 1349

MySQL Order By Number of Matches

I'm trying to order a MySQL select statement by the total number of matches in a row. For example, if the table looks like ...

id | dessert | choice
---------------------
1  | pie     | apple pie, chocolate pie 
2  | cake    | chocolate cake, lemon cake, white chocolate cake, triple chocolate cake
3  | donut   | frosted donut, chocolate donut, chocolate cream donut

... and if someone searches for chocolate, the results ought to be ordered:

dessert | matches
-----------------
cake    | 3
donut   | 2
pie     | 1

However, I'm struggling to make the query ordered like this. I've looked at other examples here, but they seem overly complicated for what I guessed would be a fairly simple thing.

Is there a straightforward way to achieve this? I'm new to MySQL, so I'm sorry if this is an obvious question.

Thanks in advance for your thoughts!

P.S. I'm not able to use fulltext on this table, hence I can't order the results by relevancy.

Upvotes: 3

Views: 3830

Answers (2)

Sodved
Sodved

Reputation: 8588

Just strip out the matching word and then compare the difference in string lengths

SELECT id
     , desert
     , ( LENGTH(choice) - LENGTH(REPLACE(choice,'chocolate','')) )
       / LENGTH('chocolate') AS matches
FROM desert_table
WHERE choice LIKE '%chocolate%'
ORDER BY 3 DESC

Upvotes: 5

awm
awm

Reputation: 6570

How about dividing your data into two tables - one defining the desserts and one defining the choices? This would make the queries simpler and faster.

Desserts table:

id | dessert
------------
1  | pie
2  | cake
3  | donut

Choices table:

id | choice
-----------
1  | apple pie
1  | chocolate pie 
2  | chocolate cake
2  | lemon cake
2  | white chocolate cake
2  | triple chocolate cake
3  | frosted donut
3  | chocolate donut
3  | chocolate cream donut

Then you could do something like:

select `dessert`, count(*) as `matches`
       from `desserts` join `choices` using (`id`)
       where `choice` like '%chocolate%'
       group by `id`
       order by `matches` desc

Upvotes: 7

Related Questions