How to use "group by" with a polymorphic relationship in Laravel?

Question

I have the following relationship:

return $this->morphedByMany('App\Models\Movie', 'listable', 'cnt_lists', 'list_id')->withPivot('id', 'order', 'updated_at')->where('movies.has_poster', true)->orderBy('order', 'desc')->withTimestamps();

That relationship produces this query:

select `movies`.*, `cnt_lists`.`list_id` as `pivot_list_id`, `cnt_lists`.`listable_id` as `pivot_listable_id`, `cnt_lists`.`id` as `pivot_id`, `cnt_lists`.`order` as `pivot_order`, `cnt_lists`.`updated_at` as `pivot_updated_at`, `cnt_lists`.`created_at` as `pivot_created_at` from `movies` inner join `cnt_lists` on `movies`.`id` = `cnt_lists`.`listable_id` where `movies`.`has_poster` = '1' and `cnt_lists`.`list_id` in ('3176', '3283', '3285', '3287') and `cnt_lists`.`listable_type` = 'App\Models\Movie' order by `order` desc

This relationship works, but it grabs many for each list. I am trying to limit to just one per list. I was originally exploring take() but that limits the whole result set, not a per list limit. I then thought I could group by the cnt_lists.list_id which would grab one per list. This seems to get me what I am looking for. Below is the query that works for me, I am just trying to figure out how to use group by in the relationship because it breaks the relationship when I add it.

select `movies`.*, `cnt_lists`.`list_id` as `pivot_list_id`, `cnt_lists`.`listable_id` as `pivot_listable_id`, `cnt_lists`.`id` as `pivot_id`, `cnt_lists`.`order` as `pivot_order`, `cnt_lists`.`updated_at` as `pivot_updated_at`, `cnt_lists`.`created_at` as `pivot_created_at` from `movies` inner join `cnt_lists` on `movies`.`id` = `cnt_lists`.`listable_id` where `movies`.`has_poster` = '1' and `cnt_lists`.`list_id` in ('3176', '3283', '3285', '3287') and `cnt_lists`.`listable_type` = 'App\Models\Movie' group by `cnt_lists`.`list_id` order by `order` desc

When I add groupBy in the relationship:

return $this->morphedByMany('App\Models\Movie', 'listable', 'cnt_lists', 'list_id')->withPivot('id', 'order', 'updated_at')->where('movies.has_poster', true)->groupBy('cnt_lists.list_id')->orderBy('order', 'desc')->withTimestamps();

I get the following error:

select `movies`.*, `cnt_lists`.`list_id` as `pivot_list_id`, `cnt_lists`.`listable_id` as `pivot_listable_id`, `cnt_lists`.`id` as `pivot_id`, `cnt_lists`.`order` as `pivot_order`, `cnt_lists`.`updated_at` as `pivot_updated_at`, `cnt_lists`.`created_at` as `pivot_created_at` from `movies` inner join `cnt_lists` on `movies`.`id` = `cnt_lists`.`listable_id` where `movies`.`has_poster` = 1 and `cnt_lists`.`list_id` in (3176, 3283, 3285, 3287) and `cnt_lists`.`listable_type` = App\Models\Movie group by `cnt_lists`.`list_id` order by `order` desc

For some reason, the model App\Models\Movie is not in quotes so it breaks the whole query. If I run the query manually it works, I just have to add quotes to the polymorphic model. i.e. "App\Models\Movie"

Answer 1

I have get ur question and answer. According to your answer, I get that the main issue is "Strict mode," which is really just the list of modes 5.7 enables by default . Some time our folks are not understood about strict mode . At https://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2 this link , you get how to Enabling and disabling strict mode in Laravel 5.2 E.g.

These settings live in config/database.php in the connections.mysql section. For starters, let's look into enabling and disabling "strict" mode:

 'connections' => [
        'mysql' => [
            // Behave like MySQL 5.6
            'strict' => false,

            // Behave like MySQL 5.7
            'strict' => true,
        ]
    ]

Also we can customize it by :

'connections' => [
        'mysql' => [
            // Ignore this key and rely on the strict key
            'modes' => null,

            // Explicitly disable all modes, overriding strict setting
            'modes' => [],

            // Explicitly enable specific modes, overriding strict setting
            'modes' => [
                'STRICT_TRANS_TABLES',
                'ONLY_FULL_GROUP_BY',
            ],
        ]
    ]

Thanks

Answer 2

Ok, so it turns out that the issue was related to MySQL strict mode, which includes ONLY_FULL_GROUP_BY mode.

I disabled strict mode at the Laravel level and the query is now working as expected. Just set to strict to false in the database config file for your connection.

'strict' => false,