How to sort custom sortable column by custom table value

Question

Okay so the solution is to use the posts_clauses filter.

Lets assume we have 2 values in our custom table that we will call custom_table that we want to sort by one bigint that we will call value and one text that we will call text.

In order to sort, we need to ensure that we have a common value in the table that can be used to join onto the main posts table. So for that we will use a third value in the table that we will call post_id. This value needs to match with a corresponding ID of what ever post you are referring too. For now we are going to be using the post post type.

I am sure this will can be expanded upon further, but that is what I went with for our simple example.

First lets start by adding the filter:

add_filter('posts_clauses', 'custom_sort_columns_clauses', 10, 2);

Then lets create our function

function custom_sort_columns_clauses($clauses, $query)
{
    // Ensure we are in admin and querying
    if ( ! is_admin() || ! $query->is_main_query() ) {
        return $clauses;
    }
    global $wpdb; //Get our global variable to gain access to our database prefix
    //Value order
    if ( $query->get( 'post_type' ) === 'post' && $query->get( 'orderby' ) === 'Value' ) 
    {
        $clauses['join'] .= "LEFT OUTER JOIN {$wpdb -> prefix}custom_table ON {$wpdb->prefix}posts.ID = {$wpdb -> prefix}custom_table.post_id";
        $clauses['orderby'] = "{$wpdb -> prefix}custom_table.value " . $query -> get('order');
    }
    //Alphabetical order
    if ( $query->get( 'post_type' ) === 'post' && $query-> get('orderby') === 'Text' )
    {
        $clauses['join'] .= "LEFT OUTER JOIN {$wpdb -> prefix}custom_table ON {$wpdb->prefix}posts.ID = {$wpdb -> prefix}custom_table.post_id";
        $clauses['orderby'] = "{$wpdb -> prefix}custom_table.text " . $query -> get('order');
    }

    return $clauses;
}

Lets break down the sauce.

if ( $query->get( 'post_type' ) === 'post' && $query->get( 'orderby' ) === 'Value' )

This ensures that we have the right orderby that we are working on. Notice the Value This should match what you see in your URL. For example:

edit.php?orderby=Value&order=asc

If it were:

edit.php?orderby=value&order=asc

use lowercase instead or what ever exists there to ensure you are working on the right orderby.

After that:

$clauses['join'] .= "LEFT OUTER JOIN {$wpdb -> prefix}custom_table ON {$wpdb->prefix}posts.ID = {$wpdb -> prefix}custom_table.post_id";

What we are doing here is using a LEFT OUTER JOIN SQL function to join both tables together (I use outer just in case there are nulls, but if you know there won’t be you don’t need OUTER). where posts.ID = custom_table.post_id

After that we need to change our order by to use our custom_table value.

we do that with:

$clauses['orderby'] = "{$wpdb -> prefix}custom_table.value " . $query -> get('order');

I use the $query -> get('order') to ensure that the direction matches our query order. i.e ASC or DESC.

Then after we have done with all our clauses we need to return our clauses with:

return $clauses remembering that $clauses is one of our parameters that we pass to the hook.

I hope that this helps anyone who had the same problem as I.

I am not really a pro programmer and only do it because I like creating things, so any edits to make this better I would love to hear!

Related Posts: