CODEWITHSUNDEEP
mysqlalgorithmlogic
user1904273
user1904273

Reputation: 4764

PHP/MYSQL Create Sort Algorithm Data Schema

My apologies in advance if this does not seem like a proper SO question. However, I can't find the answer anywhere else.

I want to be able to sort results by an algorithm that ranks results based on different columns or variables using a rule. My intuition is that using the built in MYSQL ORDER BY is much faster than manipulating the record collection once it is returned. I have used Indexes in MYSQL to speed up sorting on a field or alternatively two fields. But I don't know how to create a rule or formula that weights two fields and prioritizes and imposes logic on the weights.

For example: Say I have a table of reviews. I would like to create an algorithm that sorts these reviews based on the combined weighting of different variables with a bit of logic thrown in.

As in example: Return results edited today first. With all other results, return based 50% on the reputation of the author and 50% on when the review was created.

Table reviews:

id|reviewtext|lastedited|datecreated|authorid

Table authors

id|name|reputation

MYSQL/PHP:

$SQL = "SELECT r.*, a.* FROM `reviews` r
LEFT JOIN `authors` a
on = r.authorid= a.id
WHERE reviewtext LIKE '%str%'
ORDER BY lastedited DESC datecreated DESC reputation DESC";

would order first by lastedited, then by datecreated etc.

How do I use a more complicated formula with logic weights etc.?

Thanks in advance for any suggestions.

Upvotes: 0

Views: 493

Answers (1)

user1191247
user1191247

Reputation: 12973

You can use expressions in your ORDER BY clause -

SELECT r.*, a.*
FROM reviews r
INNER JOIN authors a
    ON r.authorid = a.id
WHERE reviewtext LIKE '%str%'
ORDER BY
    IF(lastedited >= CURRENT_DATE, lastedited, '0000-00-00') DESC,
    (SOME EXPRESSION EVALUATING DATE AND REPUTATION) DESC

Upvotes: 1

Related Questions