WP_Query: apply an SQL function to meta fileld value

Question

Say I'm doing a query with ordering by meta field:

new WP_Query( array(
    'meta_query' => array(
        'state_clause' => array(
            'key' => 'state'
        ),
    ),
    'orderby' => array( 
        'state_clause' => 'DESC',
    )
) );

Can I apply an SQL function before ordering (without manual SQL query)? Say order by not state but by LTRIM(state)?

Answer 1

You can use the filter posts_orderby. For a simple example:

add_filter('posts_orderby', 'apply_sql_on_order', 10, 2 );
function apply_sql_on_order($orderby, $query) {
    if(is_array($query->query_vars["orderby"]) && array_key_exists("apply_sql", $query->query_vars["orderby"])) {
        $orderby = preg_replace("/(.*) (ASC|DESC)/", $query->query_vars["orderby"]["apply_sql"] . "($1) $2", $orderby);
    }
    return $orderby;
}

$query = new WP_Query( array(
    'meta_query' => array(
        'state_clause' => array(
            'key' => 'state'
        ),
    ),
    'orderby' => array( 
        'state_clause' => 'DESC',
        "apply_sql" => "LTRIM"
    )
) );

Will get you from

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND ( 
  wp_postmeta.meta_key = 'state'
) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY CAST(wp_postmeta.meta_value AS CHAR) DESC LIMIT 0, 10

to

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND ( 
  wp_postmeta.meta_key = 'state'
) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY LTRIM(CAST(wp_postmeta.meta_value AS CHAR)) DESC LIMIT 0, 10

WP will simply ignore "apply_sql" in the orderby array, as it's not a valid order clause in the context of this WP_Query.

This is obviously a very rough version and will probably do weird things if you add multiple clauses into your orderby. In that case, it might be easier not to go for some kind of abstraction and simply have the filter callback just manipulate the SQL.

If you'd like to expand on this, looking at the source will probably help. Most of the magic is happening in parse_orderby in WP_Query. You could create your own version of this that will basically emulate it and add some fancy stuff on the side (possibly unset orderby on the query with pre_get_posts and then add the fancyfied order clauses back in with posts_orderby).