Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

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);
}
share|improve this question
    
OK, so I'm halfway there. My solution is to run 2 loops - the first loop is to make a multidimensional array for the post ID and the post's profitability. So I have an array called "$idsandscores" that looks like this: [209] => 0.29090909090909 [478] => 2.65 [231] => 27.84 [133] => 29 So my question is, how do I make a wordpress query ordered by the IDs in my $idsandposts array? –  user1399181 Mar 21 at 10:18
    
Edit 2 - looks like I found it! wordpress.org/support/topic/order-by-page_in-array-sequence –  user1399181 Mar 21 at 11:05

1 Answer 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);
share|improve this answer
    
Thanks! I actually decided to go this route and it's working great. I had assumed though that it was bad practice to store something in the database when it can be computed on the fly. (I have nothing to back that up, just a hunch.) anyway thanks! –  user1399181 Mar 22 at 14:12
    
The Core "transient" system is essentially "storing thing in the database what can be computed on the fly". What is bad practice is computing on the fly when that is slower than caching in the database. "Transients" would be another similar option, by the way. –  s_ha_dum Mar 22 at 14:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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