query in relationship

i have Category hasManyThrough relation on OrderItem Model OrderITem belongs to Order Model i want to query on order relationship

Category Model 
   public function orderItems()
    {
        return $this->hasManyThrough(OrderItem::class, Product::class);
    }

OrderItem Model

 public function order()
    {
        return $this->belongsTo(Order::class);
    }

my eloquent query

  public function salesByCategory()
    {

        $categories = Category::whereHas('orderItems.order', function ($query) {
            $query->where('status', 'delivered');
        })->withCount(['orderItems'])->get();

        return view('report.sales_by_category', compact('categories'));
    }

all total there is 4 result among 4 result only one is delivered so count value should be 1 but i am getting 4 can any one help me to correct the query

SilenceBringer

4 years ago

Level 55

@freemium you need to scope withCount too

        $categories = Category::whereHas('orderItems.order', function ($query) {
            $query->where('status', 'delivered');
        })->withCount(['orderItems' => function ($query) {
            $query->where('status', 'delivered');
        }])->get();

freemium

4 years ago

Level 1

@SilenceBringer sir status column is available in Order Table not in OrderItem Table so we need to query on orderItem.order order relation is belongsTo Order Model

SilenceBringer

4 years ago

Best Answer

Level 55

@freemium ok, like this

        $categories = Category::whereHas('orderItems.order', function ($query) {
            $query->where('status', 'delivered');
        })->withCount(['orderItems' => function ($query) {
            $query->whereHas('order', function ($query) {
            	$query->where('status', 'delivered');
        	});
        }])->get();

Please or to participate in this conversation.