withCount with Conditions in relation properties

Hi everybody,

I'm stuck with a problem for too long, I can't solve on my own and I haven't read about it yet.

To simplify my problem, let say I have two classes, Brand and Items :

//Brand class
class Brand extends Model
    protected $primaryKey = 'brandId';

    //Brand properties
    public $fillable = [

    // Relation hasMany
    public function items()
        return $this->hasMany(Item::Class, 'fkBrandId', 'brandId');

class Item extends Model
    protected $primaryKey = 'itemId';

    //Brand properties
    public $fillable = [

    // Relation belongsTo
    public function brand()
        return $this->belongsTo(Brand::Class, 'fkBrandId', 'brandId');

What I'm trying to do, is to get in only one request, all the Brands with a count of Items each Brand has, but counting only when the itemName property is different of the brandName property of the parent relation.

So I know the following code doesn't work at all, it's just to give you an idea of what I want to achieve if my previous explanation isn't clear enough :

$brands = Brand::withCount('items', function ($query) {
                     $query->where(brandName, '<>', itemName);

Or if someone has the solution with pure sql without using laravel functions, I could probably use a query builder to achieve it.

Thank you so much for your help !

Cameron replied 2 months ago

this may help


you may have to use a join or does brands.brandnane and items.itemname work in your where? i don't know if that query knows what the fields you are trying to compare are

Azure Hawkwood replied 2 months ago

Hi Cameron, thank you very much for your answer.

Actually in those examples (of stackoverflow or even my fake where condition), the where function tests one property of the model on one side and an actual value on the other side. And what I try to achieve is instead of a value, I want to compare it with a field of another table. I know it's impossible to do it with a simple where close.

And I think you are right, I could probably achieve it by adding a join in the closure of my withCount function, but I can't find a working syntax to properly solve my problem.

But thank you again for your time, I really appreciate !

hokutoasari replied 1 month ago

Hi, How about trying join method in withCount closure?

$brands = Brand::withCount(['items' => function (\Illuminate\Database\Eloquent\Builder $query) {
    $query->join('brands', 'brands.brandId', '=', 'items.fkBrandId');
    $query->where('brands.brandName', '<>', 'items.itemName');

Sign in to participate in this thread!

We'd like to thank these amazing companies for supporting us