0

I want to order posts by multiplying values from a custom table.

+---------+------+------+------+------+------+
| post_id |   a  |   b  |   c  |   d  |   e  |
+---------+------+------+------+------+------+
|    1    | 0.24 | 0.34 | 0.28 | 0.23 | 0.29 |
+---------+------+------+------+------+------+
|    2    | 0.25 | 0.33 | 0.26 | 0.31 | 0.31 |
+---------+------+------+------+------+------+
|    3    | 0.27 | 0.31 | 0.32 | 0.28 | 0.25 |
+---------+------+------+------+------+------+

I use the following code and it seems to be working fine:

add_filter('posts_join_paged', function( $join, $query ) {
    global $wpdb;
    $join .= "LEFT JOIN {$wpdb->prefix}rates ON {$wpdb->prefix}rates.post_id = {$wpdb->prefix}posts.ID";
    return $join;
}, 10, 2 );


add_filter('posts_orderby', function( $orderby, $query ) {
    global $wpdb;
    $orderby = "{$wpdb->prefix}rates.a*c*e ASC";
    return $orderby;
}, 10, 2 );

My question is if this way of join and multiplication is the right approach or there is a better way?

1

There’s nothing wrong with it from WPs point of view. If you want to join posts with your own table and change the way of ordering, then that’s the way to do it.

The only thing is a little bit tricky, I guess, is that you use posts_join_paged and not posts_join - it can make it a little bit harder to debug if something goes wrong, but it also makes it a little bit more optimal, so there’s nothing wrong with this.

The are two things that concern me a little bit with this approach:

  1. Will that result in a proper order if there is no row in your custom table for some posts?
  2. Wouldn’t it be nicer for DB to store that multiplication result already computed - ordering by value of o column is nicer for DB than ordering by computed value.
  • Thank you for your thoughts on this @Krzysiek. 1. I need to test this but the rates are required for every post so I will sync the two tables. 2. The result is based on user selections. – Christopher Mar 11 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.