Best practice – Meta Query vs. post_clauses for “left join” ordering

2 Views

Moreso a psuedo-code question than actual code. I have some custom post meta attached to attachments, and created a list table column for this meta. The meta is either 1, or null (not set), but I am trying to enable sorting for said column.

Is there a way to utilize meta_query for WP_Query to essentially LEFT JOIN, where I get all values (1 or null), and then I can utilize orderby=meta_value_num for ordering?

Read More

The obvious way it would work is hooking into posts_clauses, adding a JOIN, and doing the orderby as well – just curious if this would be possible while using direct WP_Query functionality!

Thanks.

Post Views: 2

Related posts

2 comments

  1. You can use pre_get_posts with a callback:

    <?php
defined( 'ABSPATH' ) OR exit;
/** Plugin Name: (#102854) Order Posts by Foo */

add_filter( 'pre_get_posts', 'wpse_102854_orderby_foo' );
function wpse_102854_orderby_foo( $query )
{
    if ( 
        ! $query->is_main_query()
        OR ! is_admin()
        OR 'edit.php?post_type=YOUR_POST_TYPE' !== $GLOBALS['parent_file']
        // Other conditions that force an abort, Example:
        // OR 'foo' !== $query->get( 'some_query_var' )
    )
        return $query;

    $query->set( 'orderby', 'meta_value_num' );
    // etc.

    return $query;
}

  2. You can do it either way!

    Using posts_clauses, manually write your JOIN statement (using $wpdb->prepare() – be safe!) , and add/customize the orderby clause as well.

    Using meta_query there are a couple things needed. First, as kaiser mentions, you must use relation within meta_query in order for it to work. Second, even though meta_query is defined, you still need to specify the meta_key argument, or else orderby=meta_value will not work. I assume this is because you can have multiple joins happening at once, which could be using different meta keys.

    Here is how I accomplished it:

    function handle_my_sortable_column( $query ) {
    global $pagenow;
    if( is_admin() && 'upload.php' == $pagenow && 'my_meta_key' == get_query_var( 'orderby' ) ) {
        $query->set( 'meta_query', array(
            'relation' => 'OR',
            array(
                'key' => my_meta_key',
                'value' => null,
                'compare' => 'EXISTS'
            ),
            array(
                'key' => 'my_meta_key',
                'value' => '', // must be '' value, null does not work
                'compare' => 'NOT EXISTS'
            )
        ) );
        $query->set( 'meta_key', 'my_meta_key' ); // required for orderby
        $query->set( 'orderby', 'meta_value_num' );
    }
}

Comments are closed.