How to return count + collection data in relationships

Hi all,

I am trying to return the completed and pending sales of each active user in a single table. But I cannot work out how to do a count while returning the collection.

Right now this returns the collection correctly $results= User::with('sales')->where('status', 'active')->get();

I was hoping for something like this:

         $results= User::with('sales')
          //count  sales.completed as completedSales
          //count  sales.pending as pendingSales
           ->where('status', 'active')
          ->get();

Is this actual possible as I cannot get it to work.

Thanks.

MichalOravec

5 years ago

Best Answer

Level 75

$posts = Post::with('sales')->withCount([
    'sales as completed_sales_count' => function ($query) {
        $query->where('type', 'completed');
    },
    'sales as pending_sales_count' => function ($query) {
        $query->where('type', 'pending');
    }
])->where('status', 'active')->get();

Docs: https://laravel.com/docs/8.x/eloquent-relationships#counting-related-models

User1980

5 years ago

Level 4

I cannot thank you enough, I was actually going in the wrong direction, this is where I was at:

User::with(['sales' => function ($query) {
            $query->selectRaw("SUM(CASE type WHEN 'completed' then 1 else null end) as completed_sales_count");
            $query->selectRaw("SUM(CASE type WHEN 'pending' then 1 else null end) as pending_sales_count");
            }])
->where('status', 'active')->get();

MichalOravec

5 years ago

Level 75

@user1980 So what I posted above is working for you? If so mark this thread as solved.

User1980

5 years ago

Level 4

My apology, i had a long call in between...this is why I am late in clicking "Best answer" :-)

Please or to participate in this conversation.