Custom column sorting on custom query result
Since admin columns 6.4, backward compatibility for custom columns has been broken.
Currently I’m attempting to update admin columns in my projects to version 6.4.
In one project, we’ve added a custom column, which performs a custom SQL query, which returns a number.
In this case, we count how many posts (WP default post type) an author (Custom post type) has written.
We have a custom table, where we have all relations stored between a post and an author.
Following the example at https://github.com/codepress/ac-column-template/pull/8/files, I’ve got the following code now for my column.
<?php
namespace PROJECTNAME\AdminColumns\AuthorPostsCount;
use AC\Column as ACColumn;
use ACP\Sorting\Sortable;
use PROJECTNAME\AdminColumns\AuthorPostsCount\Sorting;
class Column extends ACColumn implements Sortable {
public function __construct() {
$this->set_type( 'column-author-posts-count' );
$this->set_label( __( 'Author Posts Count', 'column-author-posts-count' ) );
}
public function get_value( $author_id ): string {
return $this->get_raw_value( $author_id );
}
public function get_raw_value( $author_id ) {
global $wpdb;
return (int) $wpdb->get_var(
$wpdb->prepare(
"SELECT COUNT( DISTINCT post_id ) FROM {$wpdb->prefix}authors_posts WHERE author_id = %d;",
$author_id
)
);
}
public function is_valid() {
return $this->get_post_type() === 'author';
}
public function sorting() {
return new Sorting( $this ); // No clue how to make this work
}
Now I am completely stuck at creating the sorting logic for the column.
There seems to be no example or logic in the plugin in place, that allows this to work.
Could someone please explain or give an example how that should work, to get me in the right direction?
If more information is needed, like the pre-6.4 column code, please let me know and I will provide that as well.
Thank you very much in advance!
Kind regards,
Menno
For some time now, our sorting logic relies more on altering the query with SQL. In our column template repository we had an example that just sorted based on the column value, but that way of sorting is ok when you have a small dataset but quickly becomes unusable when your dataset grows.
You could have a look at the template how we have implemented sorting at this moment.
https://github.com/codepress/ac-column-template/blob/master/classes/Column/Sorting.php
Think about how you would sort the data when you query the post table by using joins and where clauses and apply them by using the new Sorting logic.
Hi Stefan,
Thanks for your answer.
I know how I could sort the posts, however this would require me to modify the SELECT query to perform a COUNT of a JOIN, or perform another SELECT query inside the first one.
Unfortunately, I do not see how to perform such logic now, as modifying the SELECT query does not seem possible.
So basically the question becomes, can I somehow modify the SELECT query somehow?
And if not, is there a way to revert back to the old and less performant logic, sorting by the column value?
Kind regards,
Menno
Hi Menno,
For the table, I recommend not to change the ‘SELECt’ part in the main query. You probably could join a sorted subquery and order by a subfield of that query. It is good to know that the sorting logic is applied to the same query that is populating the records for the table. You could have a look at the following class that does something similar:
ACP\Sorting\Model\User\PostCount
Hi Stefan,
That’s the example I was searching for but couldn’t find myself, and was exactly what I needed.
I have been able to modify it, and it works like a charm.
Thanks a lot. Have a good day!
You must be logged in to reply to this topic.