How to sort a custom wordpress query by combination of meta values?

2 Views

I’m trying to build a custom query that’s a combination of custom key values. For example, with the code below I’m able to sort my posts by how much money they’ve received (paidusd) and how much tips they’ve received (totaltips). This code is working great.

But what I’d like to do is add another sort for “most profitable” which would be the ratio of paidusd to totaltips. Where would I perform that division equation? I’m stumped.

Read More

Any help is greatly appreciated – thanks!!!! 

if ( $_GET['sort'] == 'mostexpensive'){
    //query_posts('&meta_key=paidbtc&orderby=meta_value');
    $args = array(
                   'meta_key' => 'paidusd',
                   'orderby' => 'meta_value',
                   'posts_per_page' => 10,
                   'paged' => $paged
                   );
    query_posts($args);
}

if ( $_GET['sort'] == 'mosttipped'){
    $args = array(
                   'meta_key' => 'totaltips',
                   'orderby' => 'meta_value',
                   'posts_per_page' => 10,
                   'paged' => $paged
                   );
    query_posts($args);
}
Post Views: 2

Related posts

Leave a Reply

1 comment

  1. What you are wanting to do is possible– MySQL can do math– but WP_Query can’t handle this natively. You’d need to apply a filter or two. You would end up sorting on generated values which is not especially efficient. That is, MySQL, would read all of the rows in the table for those two keys, do some math, and then sort.

    My advice would be to store a third key– say, profitability— when you save the post keys, and then sort on that instead of trying to generate this is SQL. Something, very crudely, like this:

    function generate_profitability_wpse_138683($meta_id,$object_id,$meta_key,$_meta_value) {

  if ('paidusd' === $meta_key) {
    $tips = get_post_meta($object_id,'totaltips');
    if (!empty($tips)) {
      $profit = (int)$_meta_value/(int)$tips;
    }
  } elseif ('totaltips' === $meta_key) {
    $paid = get_post_meta($object_id,'paidusd');
    if (!empty($paid)) {
      $profit = (int)$paid/(int)$_meta_value;
    }
  }

  if (!empty($profit)) {
    update_post_meta($object_id,'profitability',$profit);
  }
}
add_action('updated_post_meta','generate_profitability_wpse_138683',10,4);