laravel: withCount return me column not found exception

Question

I defined a scope function in an Eloquent model that uses withCount in the query, I also define an unit test for this function that works properly but when I use it in my controller it throws the exception error which is

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'unused_count' in 'where clause' (SQL: select * from `coupons` where  `unused_count` > 0)

Here are my eloquent model functions:

public function unsed(): BelongsToMany
{
        return $this->belongsToMany(User::class, 'user_coupon')
            ->wherePivot('discount', '=', 0);
}

public function scopeAvailable(Builder $query): Builder
{
        return $query->where(function ($coupons) {
            return $coupons
                ->withCount('unused')
                ->where('quantity', '>', 0)
                ->orWhere('unused_count', '>', 0 );
        });
}

`withCount` expect you to pass a relationship, not a column, is `unused_count` a relationship? also please add more details so people can help — Amjad, Dec 12 '22 at 19:34
Yes, I know that, but I used the same code for unit testing and it worked — kazemm, Dec 12 '22 at 19:43
Are you actually using `unused_count` anywhere else? If you are, then what you have is fine, but if not, you can simply do `->orHas('unused')`: https://laravel.com/docs/9.x/eloquent-relationships#querying-relationship-existence. That will only return records that have at least 1 record returned for the `unused` relationship. — Tim Lewis, Dec 12 '22 at 22:11
Thank u @TimLewis, The problem arises because I want to put a relation inside another where clauses, which seems impossible. However, I got the following answer that works for me! — kazemm, Dec 12 '22 at 22:22
Yeah, `->where()` doesn't typically work on relationships, that's what the `->has()`/`->whereHas()` methods are for I'd have to see what you mean, but yes, the solution you have is also fine; I was just curious if that other approach would work for you, since it's slightly shorter/cleaner. Cheers! — Tim Lewis, Dec 12 '22 at 22:24
Yeah, I got what you mention my friend that is cleaner approach! — kazemm, Dec 12 '22 at 22:41

kazemm · Accepted Answer · 2022-12-13T05:55:38.037

I found that it related to where clause in my codes so I use having like below:

public function scopeAvailable(Builder $query): Builder
{
        return $query->where(fn($coupons) => $coupons
           ->where('quantity', '>', 0)
           ->orHas('Unused')
        );
}

I think the reason is that withCount / with clauses using select with groupBy under the hood which makes sense as here having vs where has described it.

laravel: withCount return me column not found exception

1 Answers1