When using listExtendQuery with groupby, paginate seems not work

[fanti1]

Winter CMS Build

Other (please specify below)

PHP Version

7.4

Database engine

MySQL/MariaDB

Plugins installed

No response

Issue description

Hello everyone. I have a model listing, where I need to override the standard querie, using a groupBy that returns the desired results, according to the business rule. The values ​​that appear as "totals" in the pagination are correct.

The problem is, when doing this: public function listExtendQuery($query) { $query->groupBy('column'); }

And when trying to move from one page to another, it just doesn't load. I get stuck on the first page. See: the pagination widget is loaded, but it doesn't work when I click next page...

Does anyone have any ideas?

Steps to replicate

• Use any model with some data • In the controller of above's model, use the listExtendQuery to override default querie using: $query->groupBy('some_column') • Try to click in the next step indicator

Workaround

Nothing yet.

[github-actions[bot]]

This issue will be closed and archived in 3 days, as there has been no activity in the last 60 days. If this issue is still relevant or you would like to see it actioned, please respond and we will re-open this issue. If this issue is critical to your business, consider joining the Premium Support Program where a Service Level Agreement is offered.

[maricruztm]

Hi, I have the same issue with a backend list. It has a groupBy as I'm joining more tables to the query, which gives me duplicated rows of the main table. This used to work in older versions (I was on OctoberCMS and migrated to WinterCMS 1.1.8).

I'm trying with distinct('main_table.id') but that breaks the items count. leaving the group by is fine but it breaks the pagination.

As far as I know, some logic has changed around pagination in Laravel from version 6 and therefore the issue?

Any workaround to fix this?

Thanks a lot

[mjauvin]

@maricruztm from what October version did you migrate from (the one where it worked) ?

[maricruztm]

I cannot double check that anymore but I think it was 448: https://github.com/octobercms/october/tree/v1.0.448

[github-actions[bot]]

This issue will be closed and archived in 3 days, as there has been no activity in this issue for the last 6 months. If this issue is still relevant or you would like to see it actioned, please respond within 3 days. If this issue is critical for your business, please reach out to us at [email protected].

[LukeTowers]

Is this still an issue in Winter v1.2?

[maricruztm]

Hi,

We haven't done the migration yet but will do in a few weeks. Would it be OK to leave this open until then please?

Thanks

On Mon, 24 Oct 2022, 20:49 Luke Towers, @.***> wrote:

Is this still an issue in Winter v1.2?

— Reply to this email directly, view it on GitHub https://github.com/wintercms/winter/issues/454#issuecomment-1289520382, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGM4OIQNERELHYM4YJMW2MDWE3R3RANCNFSM5OTG4DHA . You are receiving this because you were mentioned.Message ID: @.***>

[LukeTowers]

@maricruztm were you able to complete the migration to Winter v1.2 yet?

[fanti1]

Is this still an issue in Winter v1.2?

Just made a test with winter 1.2.1 and this issue continues. Any idea?

[bennothommo]

@fanti1 I've just tried your replication details and I can't replicate this. There must be something else at play here. Do you have any other conditions in the query, or are using any filters?

If possible, could you try installing the Debug Bar and provide us with a copy of the query that runs for your list (it should be the query - or queries - that runs on your model's database tables)?

[fanti1]

Hi @bennothommo. Testing with *** Detected Winter CMS build 1.2.1. I've created an model Test with this fields: Then, in controller I just do: When doing this, I need to set config/database.php strict to false, to solve some groupBy error, I don't understand exactly why... I've changed at config_list the records per page to 3, to do the test easier with few records. And now, when I try to click next, doesn't work

[github-actions[bot]]

This issue will be closed and archived in 3 days, as there has been no activity in this issue for the last 6 months. If this issue is still relevant or you would like to see it actioned, please respond within 3 days. If this issue is critical for your business, please reach out to us at [email protected].

[maricruztm]

Hi,

Sorry. I'm on maternity leave at the moment and I'm not sure if my team has done it. I'll be back in a couple of months and will give you an update then.

Thanks, Maria

On Tue, 29 Nov 2022, 20:44 Luke Towers, @.***> wrote:

@maricruztm https://github.com/maricruztm were you able to complete the migration to Winter v1.2 yet?

— Reply to this email directly, view it on GitHub https://github.com/wintercms/winter/issues/454#issuecomment-1331279192, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGM4OIT4NQ3PRF63SL56V6LWKZTJFANCNFSM5OTG4DHA . You are receiving this because you were mentioned.Message ID: @.***>

[immanolo]

Hello,

I've encountered the same issue.

As per checking with the query logs when groupBy is used, it is also appended to the count query which I think is used by the ListController for pagination.

To replicate the issue:

• Step1: Implement listExtendQuery on your controller:

    public function listExtendQuery($query)
    {        
        $query->groupBy('id');
    }

• Step2: Log the query executions:

\Illuminate\Support\Facades\DB::listen(function ($query) {
    // Get the executed SQL query
    $sql = $query->sql;
  
    if (str_starts_with(strtoupper(trim($sql)), 'SELECT')) {
        $bindings = $query->bindings;
        $time = $query->time;
  
        $fullSql = vsprintf(str_replace(['%', '?'], ['%%', '%s'], $sql), $bindings);
  
        Log::info("SQL Query: {$fullSql} Time: {$time}ms");
    }   
  });

Count query log result:

• SQL Query: select count(*) as aggregate from table_name group by id;
• Fetches the total count by group instead of one entity/row.

[github-actions[bot]]

This issue will be closed and archived in 3 days, as there has been no activity in this issue for the last 6 months. If this issue is still relevant or you would like to see it actioned, please respond within 3 days. If this issue is critical for your business, please reach out to us at [email protected].