I'm pretty new to Laravel. I have a projects table in MySQL DB represented by a model Project.php . I want to count rows based on two different column values. Each client can have multiple projects, and the status may be ongoing or completed. Let's say the client_id column (Column A) and the status column (Column B) are the two columns I want to base my counting upon.

I want to be able to count only the first row (if multiple exists) of the MySQL table where a particular client have column B value as ongoing as well as all other clients with column B values as ongoing. The image below depicts what the table looks like:

In this case, client_ids 1 and 3 have two ongoing projects each, I want to be able to count them as 1 each along with others (client_id 2). So the count for clients with ongoing projects should be 3.

I can count all rows with ongoing project with this code snippet

Project::where('status', 'Ongoing')->count();

But cannot differentiate if a client has more than 1 projects ongoing. Please kindly assist me with this.

CodePudding user response：

Get the Client id for example

$client_id
Project::where('client_id', $clientId)->where('status', 'Ongoing')->count();

CodePudding user response：

If you want to count how many clients have at least one ongoing project you essentially have two options:

1. Group by:

$count = Project::where('status', 'Ongoing')
   ->select('client_id')
   ->groupBy('client_id')
   ->count();

2. Use an eloquent relationship

If you define a relationship between the Client and Project model you can get all clients with ongoing projects:

$count = Client::whereHas('project', function ($q) {
   $q->where('status', 'Ongoing');
})->count();

The first method should be a bit faster but if you then want to retrieve the clients it's easier to adapt the 2nd method to return the clients instead of the count