Compare two numeric custom fields

4 comments

1. I think something like this, but not tested, and my SQL-foo is rather weak:

       $test = $wpdb->get_col( $wpdb->prepare(
       "
       SELECT DISTINCT    $wpdb->posts.*
       FROM               $wpdb->posts
       INNER JOIN         $wpdb->postmeta AS mt1 ON (
            wp_posts.ID = $wpdb->postmeta.post_id
       )
       WHERE              $wpdb->postmeta.meta_key = 'goals-made'
       AND(               mt1.meta_key = 'goals-against'
           AND            CAST($wpdb->postmeta.meta_value AS INT) > CAST(mt1.meta_value AS INT)
       )
   
       "
   ));
   

   This should return a list of post_ids that you can then process. But then again, it might just fail miserably.

2. If I was in your situation, I’d used a different approach.

   Simply add an hidden meta field automatically when goals_made is updated.

   E.g.

   add_action('updated_postmeta', 'update_goal_made', 20, 4);
   
   function update_goal_made( $meta_id, $object_id, $meta_key, $_meta_value ) {
     if ( $meta_key != 'goals-made') return; // run only when update meta 'goals-made'
     // get the goals-against for post
     $against = get_post_meta($object_id, 'goals-against', true) ? : 1;
     // if 'goals-made' > 'goals-against' create/update a field '_goals-won' setted to 1
     if ( intval($_meta_value) > intval($against) ) {
       update_post_meta($object_id, '_goals-won', '1');
     } else {
       // if not delete '_goals-won' post meta (if exists)
       delete_post_meta($object_id, '_goals-won');
     }
   }
   

   Now to retrieve the posts where _goals-won exists, simply run a meta query:

   $args = (
     'posts_per_page' => -1,
     'meta_query' => array( array('meta_key'=>'_goals-won') )
   );
   $won_posts = get_posts($args);
   

   ---

   EDIT

   After some works on GhostToast answer I can give you also the right SQL query to get the same result.

   global $wpdb;
   $won_posts = $wpdb->get_results( "
       SELECT $wpdb->posts.* 
           FROM $wpdb->posts
       INNER JOIN $wpdb->postmeta AS mt1 ON ( $wpdb->posts.ID = mt1.post_id )
       INNER JOIN $wpdb->postmeta AS mt2 ON ( $wpdb->posts.ID = mt2.post_id )
       WHERE mt1.meta_key = 'goals-made' 
           AND ( 
               mt2.meta_key = 'goals-against'
               AND CAST( mt1.meta_value AS UNSIGNED ) > CAST( mt2.meta_value AS UNSIGNED )
           )
       GROUP BY $wpdb->posts.ID
   " );
   

   ---

   Side note:

   If someone write the SQL statement that handle your request, sure that SQL statement will be less performant then the simplest meta query like the first posted.

3. Hope I didn’t understand the question wrong 🙂

   $results = new WP_Query( array(
       'post_type'  => 'matches',
       'meta_query' => array(
           array(
               'key'     => 'goals-made',
               'value'   => (int) get_post_meta( get_the_ID(), 'goals-against',  true ),
               'type'    => 'INT',
               'compare' => ">",
           )
       ),
   ) );
   if ( $results->have_posts() )
   {
       while ( $results->have_posts() )
       {
           the_post();
           var_dump( $GLOBALS['post'] );
       }
   }
   

   As we don’t know from where you’re doing this query, simply replace get_the_ID() inside get_post_meta() with the retrieved ID of the posts you want to compare against.

4. Maybe I don’t understand the question either, because the answer is obvious to me. I assume this is for one post, so you’d have 2 custom fields, foo and bar.

   So in code…

   $foo = get_field('foo');
   $bar = get_field('bar');
   if ($foo > $bar) {
       echo 'foo is greater than bar';
   } else {
       echo 'bar is greater than foo';
   }
   

Comments are closed.