0

I have a custom table with data in it that I am showing in a column. I would like to make this column sortable.

Lets say that the custom table has three values for the sake of simplicity.

row_id, post_id, value

Where, post_id is the id of a post somwhere on the site.

Now in that post types custom column I show the value of the new table, and I would like to sort the the column by that value.

The way I have been trying to do it is to do a LEFT OUTER JOIN on the 'join' of the WP_query. (I have tried on pre_get_posts action and the post_clauses action.)

However I have been stuck on this problem for quite a while.

How would you go about sorting the column by the data found in this custom table? (Without resorting to meta values)

Let me know if that makes sense.

Thanks.

1 Answer 1

0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.