CODEWITHSUNDEEP
mysqldatabasedjangosorting
eugene
eugene

Reputation: 41665

sorting a query result from DB by custom function

I want to return a sorted list from DB. The function that I want to use might look like 

(field1_value * w1 + field2_value * w2) / ( 1 + currentTime-createTime(field3_value))

It is for sort by popularity feature of my application.

I wonder if how other people do this kind of sorting in DB(say MySQL)

I'm going to implement this in django eventually, but any comment on general direction/strategy to achieve this is most welcomed.

I put the time variable because I wanted newer posts get more attention.

Upvotes: 1

Views: 126

Answers (1)

eggyal
eggyal

Reputation: 125865

  1. Do I define a function and calculate score for rows for every requests?

    You could do, but it's not necessary: you can simply provide that expression to your ORDER BY clause (the 1 + currentTime part of the denominator doesn't affect the order of the results, so I have removed it):

    ORDER BY (field1 * w1 + field2 * w2) / UNIX_TIMESTAMP(field3) DESC

    Alternatively, if your query is selecting such a rating you can merely ORDER BY the aliased column name:

    ORDER BY rating

  2. Do I set aside a field for this score and calculate score in a regular interval?

    I don't know why you would need to calculate at a regular interval (as mentioned above, the constant part of the denominator has no effect on the order of results)—but if you were to store the result of the above expression in its own field, then performing the ORDER BY operation would be very much faster (especially if that new field were suitably indexed).

Upvotes: 1

Related Questions