[wp-meta] [Making WordPress.org] #1026: WordCamp wc-post-types: Session Table in Backend shows no sessions when ordering by Time

[Making WordPress.org]

#1026: WordCamp wc-post-types: Session Table in Backend shows no sessions when
ordering by Time
----------------------+--------------------------------------
  Reporter:  pixolin  |      Owner:  iandunn
      Type:  defect   |     Status:  accepted
  Priority:  normal   |  Component:  wordcamp.org
Resolution:           |   Keywords:  good-first-bug has-patch
----------------------+--------------------------------------
Changes (by SergeyBiryukov):

 * keywords:  needs-patch good-first-bug => good-first-bug has-patch


Comment:

 The current query:
 {{{
 SELECT SQL_CALC_FOUND_ROWS wc_46_posts.ID FROM wc_46_posts LEFT JOIN
 wc_46_postmeta ON ( wc_46_posts.ID = wc_46_postmeta.post_id ) LEFT JOIN
 wc_46_postmeta AS mt1 ON ( wc_46_posts.ID = mt1.post_id ) LEFT JOIN
 wc_46_postmeta AS mt2 ON (wc_46_posts.ID = mt2.post_id AND mt2.meta_key =
 '_wcpt_session_time' ) WHERE 1=1 AND (
 wc_46_postmeta.meta_key = '_wcpt_session_time'
 AND
 (
 ( mt1.meta_key = '_wcpt_session_time' AND CAST(mt1.meta_value AS CHAR) =
 '' )
 OR
 mt2.post_id IS NULL
 )
 ) AND wc_46_posts.post_type = 'wcb_session' AND (wc_46_posts.post_status =
 'publish' OR wc_46_posts.post_status = 'future' OR wc_46_posts.post_status
 = 'draft' OR wc_46_posts.post_status = 'pending' OR
 wc_46_posts.post_status = 'cancel' OR wc_46_posts.post_status = 'failed'
 OR wc_46_posts.post_status = 'timeout' OR wc_46_posts.post_status =
 'refund' OR wc_46_posts.post_status = 'private') GROUP BY wc_46_posts.ID
 ORDER BY wc_46_postmeta.meta_value+0 ASC LIMIT 0, 20
 }}}

 Note `AND CAST(mt1.meta_value AS CHAR) = ''`.

 Per https://codex.wordpress.org/Class_Reference/WP_Query, there's no need
 to specify a value when using the 'EXISTS' or 'NOT EXISTS' comparisons in
 WordPress 3.9 and up.

 A correct query would be:
 {{{
 SELECT SQL_CALC_FOUND_ROWS wc_46_posts.ID FROM wc_46_posts LEFT JOIN
 wc_46_postmeta ON ( wc_46_posts.ID = wc_46_postmeta.post_id ) LEFT JOIN
 wc_46_postmeta AS mt1 ON ( wc_46_posts.ID = mt1.post_id ) LEFT JOIN
 wc_46_postmeta AS mt2 ON (wc_46_posts.ID = mt2.post_id AND mt2.meta_key =
 '_wcpt_session_time' ) WHERE 1=1 AND (
 wc_46_postmeta.meta_key = '_wcpt_session_time'
 AND
 (
 mt1.meta_key = '_wcpt_session_time'
 OR
 mt2.post_id IS NULL
 )
 ) AND wc_46_posts.post_type = 'wcb_session' AND (wc_46_posts.post_status =
 'publish' OR wc_46_posts.post_status = 'future' OR wc_46_posts.post_status
 = 'draft' OR wc_46_posts.post_status = 'pending' OR
 wc_46_posts.post_status = 'cancel' OR wc_46_posts.post_status = 'failed'
 OR wc_46_posts.post_status = 'timeout' OR wc_46_posts.post_status =
 'refund' OR wc_46_posts.post_status = 'private') GROUP BY wc_46_posts.ID
 ORDER BY wc_46_postmeta.meta_value+0 ASC LIMIT 0, 20
 }}}

 See [attachment:meta-1026.patch].

--
Ticket URL: <https://meta.trac.wordpress.org/ticket/1026#comment:2>
Making WordPress.org <https://meta.trac.wordpress.org/>
Making WordPress.org