How do you query wordpress posts using a math formula between multiple meta field values?

Question

The following scenario is entirely fictitious, but it illustrates what I'm working on perfectly. Imagine that you have a school syllabus set up in WordPress. The students can turn in assignments early and get graded on them. Once they have a grade on it, if they are still well before the deadline, they can continue to improve their assignment and then get a new grade on it. (Again, this is make believe).

Imagine I have 2 meta fields. The first is a number that relates to how important an assignment is. The second is the current grade of the assignment. I want to query posts by these two meta fields creating a score that multiplies the assignment's importance by the assignment's improvement potential (100 - current grade).

To summarize, I'd like to query posts by meta fields using the following scoring formula (where each variable represents a meta field key):

assignment_value * (100 - current_grade )

So now, if you have an assignment with a zero and it is worth half of your grade, it will get the highest score and come to the top of the priority list to be presented to the user.

So we have posts in the database. We have 2 meta fields that we want to join via the above math formula while querying those posts. How can such a thing be accomplished? Thanks.

Answer 1

I think the easiest way would be to create a third meta field for "score" and use the WordPress update_post_meta() function to automatically calculate its value, based on the values of assignment_value and current_grade.

Exactly how to do that has been answered well in this response: https://wordpress.stackexchange.com/a/54068/16

Once you have your third "Score" meta field, you can then do a normal wp_query() keyed and ordered off of that field.