How to sort posts by custom calculated field?

Sorry for the slow reply.

You’re saying you want to sort by a calculated field, but you do not know that field’s value and the formula to calculate it also has an unknown? That’s illogical. If you can define a logical sort criteria, you might be able to sort in SQL.

In PHP you can sort any array of posts by any logical criteria you like because you define the criteria in a callback function. Use the PHP function usort() to then do the sorting based on the return value of your callback.

The calculation involves fields drom both posts and the logged in user. So the value must be calculated for each user and post. And then sorted before displayed.

Ah! So the field can be calculated on the fly, it just cannot be saved with the post. That’s fine, usort() remains the solution. Calculate the value in your callback for each of the 2 passed posts and return an appropriate value. usort() takes care of the rest.

Would I need to include blank fields in the posts, so that I could place the calculated fields in them? Or would I append the calculated fields to the sql array?

You’re misunderstanding what I’m suggesting. I propose you get the posts by whatever means you normally do. They will not be sorted correctly at this point. All that’s necessary is all posts need to be in an indexed array. Pass this array and the name of your custom comparison function to usort(). Your array is passed by reference, meaning usort() acts on it directly. No need to collect the returned value (it only indicates success or failure) When usort() finishes, your array will be properly sorted.

Your comparison function is passed two posts from the array. Determine the calculated value by whatever means for each post and return the difference. usort() will order the posts depending on this difference being positive, negative, or 0. I believe it uses a bubble sort algorithm.

You do not need a specific place to store the calculated value in the post object. You only need local variables to use in determining the calculated difference between the two passed posts. It’s likely the calculated value for any given post would be recalculated several times before the sorting is done because that post could be passed to your comparison function several times by usort(). That’s just the way it works. It’s not very efficient, but it’s very flexible in application.

Thank you, I understand what you are talking about now. I think, I know how to write the code to do this with my existing code. I will probably in the long run, have to figure out a way to make it more efficient, but for now this will work great.

Thanks again.

You’re welcome!

Something more efficient would maybe be close to what you were originally thinking. There may be a way to sort by modifying the SQL query. It depends on what data you need and what the calculation is. SQL has some ability to calculate as part of the query. You still do not need and empty container of field, the calculation is still done on the fly. That sort of query is beyond my meager SQL skills.

I guess it will not matter if I tell you what I am wanting to do since my website is already live. I am trying to sort jobs by distance using the wp job manager and the jobify theme. I have created a custom plugin that calculated the direct distance from the users location to the job location. Now I want to the job posts sorted by distance. I use the Longitude and Latitude of the user’s location and the Longitude and Latitude of the job to determine the direct distance.

you can see the site at https://contractwork.co

Nice!

If geographic distance is the sort criteria, Luis’ SQL procedure should work for you. Don’t be put off by the topic title, this is unrelated to REST.