SHARE
TWEET

Order By Multiple Meta Fields

a guest Jun 24th, 2015 234 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.     $args = array(
  2.         'posts_per_page' => -1,
  3.         'post_type' => 'cpt_program',
  4.         'meta_query' => array(
  5.             'relation' => 'OR',
  6.             'ongoing' => array(
  7.                 'key'     => 'prog_ongoing',
  8.                 'value'   => 1
  9.             ),
  10.             'start_date' => array(
  11.                 array(
  12.                     'key' => 'prog_date_start',
  13.                     'value'   => date('Ymd'),
  14.                     'type'    => 'numeric',
  15.                     'compare' => '>='
  16.                 )
  17.             )
  18.         ),
  19.         'orderby' => 'start_date ongoing',
  20.         'order'   => 'ASC',
  21.     );
  22.  
  23. Returns this:
  24.    
  25. Date: (1)
  26. Date: 04/10/15 (1)
  27. Date: (1)
  28. Date: (1)
  29. Date: (1)
  30. Date: 04/21/15 (1)
  31. Date: 06/11/15 (1)
  32. Date: (1)
  33. Date: 06/11/15 (1)
  34. Date: 06/23/15 ()
  35. Date: 06/30/15 ()
  36.  
  37. I'm not sure what determines the order when the prog_date_start is not set?
  38.  
  39. ===================
  40.  
  41. Results I'd like:
  42.  
  43. Date: 06/23/15 () // Not prog_ongoing comes first, ordered by prog_date_start
  44. Date: 06/30/15 ()
  45. Date: 04/10/15 (1) // prog_ongoing ordered by prog_date_start (those with set prog_date_start first)
  46. Date: 04/21/15 (1)
  47. Date: 06/11/15 (1)
  48. Date: (1) (all prog_ongoing without set prog_date_start at the end)
  49. ""
  50.  
  51. ====================
  52.  
  53. If I use the following I get real close.
  54.  
  55. $meta_key1 = 'prog_ongoing';
  56. $meta_key2 = 'prog_date_start';
  57. $start_date = date('Ymd');
  58.  
  59. $postids = $wpdb->get_col( $wpdb->prepare(
  60.     "
  61.     SELECT      DISTINCT key1.post_id
  62.     FROM        $wpdb->postmeta key1
  63.     INNER JOIN  $wpdb->postmeta key2
  64.                 ON key2.post_id = key1.post_id
  65.                 AND key2.meta_key = %s
  66.     WHERE       key1.meta_key = %s
  67.                 AND key1.meta_value is TRUE
  68.                 OR key2.meta_value >= %d
  69.     ORDER BY    COALESCE(NULLIF(key1.meta_value, 0), 0) DESC, COALESCE(NULLIF(key2.meta_value, ''), $start_date) ASC, key2.meta_value ASC
  70.     ",
  71.     $meta_key2,
  72.     $meta_key1,
  73.     $start_date
  74. ) );
  75.  
  76. Returns this:
  77.  
  78. Date: 06/30/15 () // These top two are the problem. They don't order by prog_date_start. prog_ongoing equals 0 with these two.
  79. Date: 06/23/15 ()
  80. Date: 04/10/15 (1)
  81. Date: 04/21/15 (1)
  82. Date: 06/11/15 (1)
  83. Date: (1) (all prog_ongoing without set date at the end)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top