CODEWITHSUNDEEP
phpmysqlsql
Tom
Tom

Reputation: 6697

How to do math formula in the sql query for desc sorting (res.a div res.b)

I have a large mySql table with user given finger up/down ratings. At the moment I have two basic sorting options for table to choose from:

 ['likes desc', 'id desc'],
 ['dislikes desc', 'id desc'],

I would like to add 3rd sorting option, that would convert above n/o of likes OR dislikes to one easy 1..5 star rating system with this kind of formula:

$rank = ($this->getLikes() / ($this->getDislikes() + $this->getLikes()))*5 ;

What is the highest performance option to do this math on sql level itself, would it be possible to do sorting for "result.likes div result.dislikes" formula inside the SQl query itself, or should I make a few queries to get a proper output somehow? I dont want to make a third column for this purpose.

Upvotes: 0

Views: 70

Answers (3)

Nico Haase
Nico Haase

Reputation: 12112

This will do the job, without a new column:

SELECT *, (likes/(likes+dislikes))*5 AS rank FROM tableName order by rank DESC

Upvotes: 3

Syedsulaiman M
Syedsulaiman M

Reputation: 63

So one thing i assume you can do is get the needed data by applying where clause and use it as tmp table and apply order by on this. Like below

SELECT * FROM (SELECT * FROM products where name like '%bottle%') tmp ORDER BY (tmp.likes/(tmp.likes+tmp.dislikes))*5;

Upvotes: 1

Martin Schneider
Martin Schneider

Reputation: 3268

You can use the formula directly in the order by clause:

ORDER BY
  (likes/(likes+dislikes))*5

If this is performant is up to you to check, this depends on many factors.

Upvotes: 2

Related Questions