[wp-trac] [WordPress Trac] #29447: Multiple options for 'orderby' => 'meta_value_num' or making it optional

[WordPress Trac]

#29447: Multiple options for 'orderby' =>  'meta_value_num' or making it optional
-------------------------+-----------------------------
 Reporter:  wzislam      |       Owner:
     Type:  enhancement  |      Status:  new
 Priority:  normal       |   Milestone:  Future Release
Component:  Query        |     Version:  3.9.2
 Severity:  normal       |  Resolution:
 Keywords:  needs-patch  |     Focuses:  performance
-------------------------+-----------------------------
Changes (by boonebgorges):

 * keywords:   => needs-patch
 * focuses:  template, performance => performance
 * type:  defect (bug) => enhancement
 * milestone:  Awaiting Review => Future Release


Comment:

 WP does support multiple orderby and order params, since WP 4.0. See
 https://make.wordpress.org/core/2014/08/29/a-more-powerful-order-by-in-
 wordpress-4-0/.

 But your case is different. The root problem has to do with ordering by
 *meta*, and the way that orderby=meta_value and orderby=meta_value_num
 work. These params generate SQL that looks like this: `SELECT ... FROM
 wptests_posts INNER JOIN wptests_postmeta ON wptests_posts.ID =
 wptests_postmeta.post_id WHERE ... wptests_postmeta.meta_key = 'foo'`. In
 other words: the JOIN that enables the ORDER BY clause also excludes posts
 that don't have meta_key=foo at all. Multiple sort order criteria for
 meta, while cool, would not solve this.

 So, the first enhancement that might come out of this ticket is: the
 ability to sort by meta_value without requiring that all the items
 returned have the meta_value. In theory, it's possible to do most of this
 with a LEFT JOIN. Take the following data:

 Posts:
 {{{
 ---------   ------------
 | Posts |   | Postmeta |
 ---------   ------------
    ID         post_id  meta_key  meta_value
    --         -------  --------  ----------
    1            2        foo       55
    2            3        foo       75
    3
    4
 }}}

 You can run the following query to get a list of posts that is ordered by
 meta_value without requiring it:

 {{{
 SELECT wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON (wp_posts.ID =
 wp_postmeta.post_id AND wp_postmeta.meta_key) ORDER BY
 wp_postmeta.meta_value DESC, wp_posts.ID ASC;
 }}}

 This does return all posts, and 2 and 3 will be correctly ordered by
 meta_value. But the IDs that do not join against any rows in the postmeta
 table will always show up at the top of the list:

 {{{
 1
 4
 3
 2
 }}}

 As far as I can see, there's no way to get around this in a single SQL
 query (without doing a further subquery or two to determine an exact
 order, or creating a temporary table). Though I might be wrong about this.
 It would probably be possible to write something in PHP that would first
 run a query against wp_postmeta, and then if necessary, would pad out the
 results with a query against wp_posts. But this is pretty clunky and will
 require some fairly broad changes to WP_Query.

 As for the issue of multiple sort orders and meta_value: That being said,
 I would also like to have it too, though it wouldn't help with your
 specific issue (that you want to return results that don't have any value
 for your key). I brought this up myself a few weeks ago, and sketched a
 solution https://core.trac.wordpress.org/ticket/25538#comment:26 I'll
 paste the relevant bits here for posterity's sake:

 > In the future, I think a better strategy for handling order + meta_query
 would involve the following:
 >
 > As WP_Meta_Query transforms meta_query clauses into SQL, it should store
 an array of the table aliases it creates, and they should get passed back
 to WP_Query
 > When WP_Query builds ORDER BY out of orderby=meta_value, it should do
 some logic like this:
 > * meta_value corresponding to which column? For this, look at the
 meta_key query var
 > * Then look up the table alias for that meta_key in the array passed
 from WP_Meta_Query::get_sql()
 > * Use that table alias when building the ORDER BY clause ("ORDER BY
 mt2.meta_value")
 > * Maybe build in support for multiple orderby, for tie-breaking etc
 ("ORDER BY mt2.meta_value ASC, mt1.meta_value DESC")

 It's really this last bullet point that addresses your request, but the
 first couple bullet points are sorta prerequisites.

 I'm going to leave this ticket open in Future Release as a placeholder for
 some of this latter work that I'd like to take up at some point in the
 not-too-distant future. If you have any thoughts on the above, I'm all
 ears :)

--
Ticket URL: <https://core.trac.wordpress.org/ticket/29447#comment:1>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform