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?
Share Improve this question edited Mar 10, 2019 at 10:58 Jacob Peattie 44.3k10 gold badges50 silver badges64 bronze badges asked Mar 10, 2019 at 10:38 ChristopherChristopher 3353 silver badges14 bronze badges1 Answer
Reset to default 2There’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:
- Will that result in a proper order if there is no row in your custom table for some posts?
- 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.