Framework: Problem with the `select` in global scopes and `withCount`

Created on 2 Dec 2017 · 5Comments · Source: laravel/framework

Laravel Version: 5.5.22
PHP Version: 7.0.26
Database Driver & Version: 5.7.18

Description:

I'm not sure if this is a bug, but I think it is. I have a global scope with the select statement. When try to using a model which uses that global scope with the ->withCount('views')->latest('views_count'), I got Unknown column 'views_count' in 'order clause'. If the only way is to remove the select from the global scope? I hope no and hope you'll suggest the proper way to fix this issue :)

Steps To Reproduce:

Create a global scope with the select statement:

$builder->where('section_id', 1)
        ->select([
            'id',
            'column_1',
            'column_2',
            '...',
            'draft',
            'published',
            'updated_at',
            'deleted_at',
            'deleted_at',
        ]);

Apply the created scope to a model.

class SomeModel extends Model {
// ...
static::addGlobalScope(new SomeScope);
// ...

Try to use that model with the withCount and order by that aggregate:

SomeClass::whereHas('views')
         ->withCount('views')
         ->latest('views_count')
         ->latest('id')
         ->get();

See the result :)

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'views_count' in 'order clause

Thanks in advance!

Source

krecebli

Most helpful comment

You could try adding to the select instead of setting the select.

"If your global scope is adding columns to the select clause of the query, you should use the addSelect method instead of select. This will prevent the unintentional replacement of the query's existing select clause.
Laravel 5.5 Docs - Eloquent - Query Scopes Blue Box

lagbox on 13 Dec 2017

👍3

All 5 comments

Could you paste the SQL representation of the query here? You can use $query->toSql(); for that.

luiz-brandao-jr on 7 Dec 2017

I've experienced some strange behavior with 'withCount' in the past (#19388). Perhaps these issues are related?

mcbanderson on 7 Dec 2017

You could try adding to the select instead of setting the select.

"If your global scope is adding columns to the select clause of the query, you should use the addSelect method instead of select. This will prevent the unintentional replacement of the query's existing select clause.
Laravel 5.5 Docs - Eloquent - Query Scopes Blue Box

lagbox on 13 Dec 2017

👍3

Sorry for the late reply.

@luiz-brandao-jr Sure:

select `posts`.`id`, `user_id`, `section_id`, `category_id`, `image_id`, `video`, `featured`, `draft`, `published`, `posts`.`created_at`, `posts`.`updated_at`, `posts`.`deleted_at`, `cover_id`
from `posts`
where exists (select * from `views` where `posts`.`id` = `views`.`post_id`)
and `published` = 1
and `draft` is null
and `posts`.`deleted_at` is null
order by `views_count` desc, `id` desc, `created_at` desc
limit 24
offset 0

@lagbox I tried addSelect, it doesn't work as expected in this situation. It works without errors, but it makes useless select in global scope because the result also contains posts.*, column1, column2 etc

krecebli on 24 Dec 2017

Well this is old but maybe will help others the solution to this problem is a callback.

SomeClass::whereHas('views')
         ->withCount(['views' => function($query)
             $query->withoutGlobalScope();
          ])
         ->latest('views_count')
         ->latest('id')
         ->get();