Confused by grouping in ListOperation

[benwrigley]

Brand new to backpack still, and trying to understand the grouping option.

I have a polymorphic Flag table with flaggable_id and flaggable_type

I want to group my query by both of these fields and then order them by the highest count.

    protected function setupListOperation()
    {

        // Group the flags by flaggable_id and flaggable_type and select the necessary columns
        CRUD::addClause('select', 'flaggable_id', 'flaggable_type', DB::raw('count(*) as flag_count'));
        CRUD::groupBy(['flaggable_id', 'flaggable_type']);
        CRUD::orderBy('flag_count', 'desc');

        $this->crud->addColumns([
            [
                'name' => 'flaggable_type',
                'label' => 'Model Type',
                'type' => 'custom_html',
                'value' => function($entry) {
                    return class_basename($entry->flaggable_type); 
                },
            ],
            [
                'name' => 'flag_count',
                'label' => 'Flag Count',
                'type' => 'number', 
            ],
            [
                'name' => 'post_message',
                'label' => 'Post Message',
                'type' => 'custom_html',
                'value' => function($entry) {
                    if ($entry->flaggable_type === 'App\\Models\\Post') {
                        $message = \App\Models\Post::find($entry->id)->message;
                        return $message;
                    }
                    return ''; // Return an empty string if the flaggable_type is not a post
                },
            ],
        ]);
    }

The query this produces still tries to order by 'id'

select `flaggable_id`, `flaggable_type`, count(*) as flag_count from `flags` group by `flaggable_id`, `flaggable_type` order by `flag_count` desc, `id` desc limit 10

I'm not sure what I'm doing wrong here. Any help appreciated.

[pxpm]

Hello @benwrigley

We always apply the order by primary key as the last order (if no order by primary key is specified by developer). Any order you define will always take precedence, so in your example everything would be ordered by flag_count, and those records that have the same flag_count will be ordered by id.

Hope that helps 👍

[benwrigley]

Hi @pxpm

Thanks so much for getting back to me!

I do understand what you mean but I still don't understand how that can work when there is a grouping clause? Because I am grouping by flaggable_id and flaggable_type, I cannot select the primary key as it cannot be aggregated and so including id in the orderby clause becomes illegal.

Forgive me if I'm not understanding something.

The query that backpack is producing fails with this mysql error:

Query 1: Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'flags.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

select `flaggable_id`, `flaggable_type`, count(*) as flag_count from `flags` group by `flaggable_id`, `flaggable_type` order by `flag_count` desc, `id` desc limit 10

Thanks again for your help!

[pxpm]

Thanks @benwrigley you are right. I missed the group by part.

If the query has a group by there is no sense for us to add the order by id. It doesn't break without sql_mode=only_full_group_by but it's not doing much anyway.

I've tagged backpack/crud 6.7.27 with that fix.

CRUD::addClause(function($query) {
            $query->select('commentable_id', 'commentable_type', DB::raw('count(*) as comment_count'));
            $query->groupBy(['commentable_id', 'commentable_type']);
        });

        CRUD::orderBy('comment_count', 'desc');

        CRUD::column('comment_count');
        CRUD::column('commentable_type');
        CRUD::column('commentable_id')->type('text');

Let me know if you are experiencing other issues.

Cheers 👍

[benwrigley]

hi @pxpm,

Thanks for looking into this more.

I've upgraded to 6.7.29 and I've swapped your example above for my tables:

        CRUD::addClause(function($query) {
            $query->select('flaggable_id', 'flaggable_type', DB::raw('count(*) as flag_count'));
            $query->groupBy(['flaggable_id', 'flaggable_type']);
        });

        CRUD::orderBy('flag_count', 'desc');

        CRUD::column('flag_count');
        CRUD::column('flaggable_type');
        CRUD::column('flaggable_id')->type('text');

And now I get a different SQL issue. This is the query that it attempts to run now:

select count(*) as total_rows from (select `flags`.*, count(*) as flag_count from `flags` group by `flaggable_id`, `flaggable_type`) as `flags_aggregator`

And the error is now:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'flags.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Thanks again!

[pxpm]

Hey @benwrigley thanks for the report.

The crud I tested this had the showEntryCount disabled so I didn't got this error sorry. I played with this for a bit and I was able to somewhat make it work, but we don't have much tests on this part of the crud and my spider sense is telling me that's highly possible the change I did in: Laravel-Backpack/CRUD#5628 could break some scenario not covered yet by the tests.

What I would suggest you do for now is to add into setupListOperation CRUD::setOperationSetting('showEntryCount', false); (it will just stop backpack from trying to count the entries). I will be somewhat away this week so I would prefer not to merge this until I spent more time testing this.

You can subscribe to that PR thread so that you get notified when I work on that PR 🙏

Thanks