wp query - Order by custom table and multiplication

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?

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?

• wp-query
• filters
• database
• mysql
• wpdb

Share Improve this question edited Mar 10, 2019 at 10:58 Jacob Peattie 44.3k1010 gold badges5050 silver badges6464 bronze badges asked Mar 10, 2019 at 10:38 ChristopherChristopher 33533 silver badges1414 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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.