[Resolved] Split: add group by to View query

[Author]

Posts

[gianniB-5]

Hello,
thanks for reply! I'm on view block. This is fine now.
I have another view (always view block editor) and I want to execute the classic group by post_title query in the view loop. In this way even if I have multiple items with the same title, I want to show it only once.
Please advice.
Thanks,
Gianni

[Nigel]

Hi there

You can't manipulate the SQL clauses from the Views UI, nor directly using the Views API. (You can modify the query arguments that get passed to WP_Query, the WordPress Class that translates query arguments into the SQL statement.)

You can, though, modify the SQL clauses using core WordPress hooks.

In this case, you could use the posts_groupby hook (https://developer.wordpress.org/reference/hooks/posts_groupby/) to add a GROUP BY clause.

If you turn on Views debugging (go to Toolset > Settings > Front-end content) then you'll be able to see both the query arguments for your View and also the resulting SQL statement. Assuming your database table prefix is the default 'wp_' then you'll see the SQL statement includes SELECT wp_posts.*, so you can add wp_posts.post_title in a GROUP BY clause.

One concern you will have with that hook is to only apply it when needed. There may be enough specific details in $query for that, but otherwise you could only set up the hook from a Views API hook where you have established (via the view id) that this is for the right query, e.g. you could use the wpv_filter_query not to make any changes to the View arguments but to simply add your callback to the posts_groupby hook.

https://toolset.com/documentation/programmer-reference/views-filters/#wpv_filter_query

Obviously, this assumes that you are familiar with manipulating queries and SQL, which I suspect you are from your question.

[gianniB-5]

Hello,
thanks for your kind reply. Debugging the query I see that there is already GROUP BY wp_posts.ID
I need to replace the ID with post_title before it is executed.

With this
add_filter( 'wpv_filter_query', 'corsiraggruppa', 101, 3 );

function corsiraggruppa( $query_args, $view_settings, $view_id ) {
if($view_id = 731) {
.......
}
return $query_args;
}
How may I make a replace?

Best,
Gianni

[gianniB-5]

I found this solution shared by Waqar
add_filter( 'wpv_filter_query', 'myfilter', 99, 3 );
function myfilter( $query_args, $settings, $view_id ) {
if($view_id == 999){
if( !isset($_GET['wpv_view_count']) ) {
add_filter('posts_groupby', 'filter_title');
}
}
return $query_args;
}

function filter_title($groupby) {
global $wpdb;
$groupby = " {$wpdb->posts}.post_title";
remove_filter('posts_groupby', 'filter_title');
return $groupby;
}
It works but is still showing page number in the footer that repeat same posts on other pages. In addition I'm using this with a view search and If I run a search it seems to ignore the grouping for title.
Please advice,
Gianni

[Luo Yang]

Hello,

Please try to change these lines of your PHP codes from:

...
if( !isset($_GET['wpv_view_count']) ) {
add_filter('posts_groupby', 'filter_title');
}
...

To:

...
add_filter('posts_groupby', 'filter_title');
...

And test again, so it won't skip your custom PHP codes when users are doing search