customization - Best practice - Meta Query vs. post_clauses for "left join" ordering

admin管理员组

文章数量:1122846

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?

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.

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?

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.

• meta-query
• customization

Share Improve this question asked Jun 13, 2013 at 13:21 Eric HolmesEric Holmes 3,90922 gold badges2222 silver badges3535 bronze badges 2

• Why are you adding a custom column (and to which table) when there's the post_meta table anyway? One entry equals one row in this table. – kaiser Commented Jun 13, 2013 at 13:29
• Media List Table, not database table – Eric Holmes Commented Jun 13, 2013 at 13:30

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

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;
}

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' );
    }
}

After WordPress 3.9 you can order on a meta_key without missing posts that do not have the metadata with :

// Don't set the 'meta_key'
// otherwise post that don't have the meta will be missing
// $query->set('meta_key', 'the meta key');
$query->set('orderby', 'meta_value');
$query->set('meta_query', [
    'relation' => 'OR',
    ['key' => 'the meta key', 'compare' => 'EXISTS',],
    ['key' => 'the meta key', 'compare' => 'NOT EXISTS'],
]);

本文标签： customizationBest practiceMeta Query vs postclauses for quotleft joinquot ordering