Question 15926 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to count number of DB table rows where column B value is "x" for the first occurrence of column A values in Laravel

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:

enter image description here

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.

Tags:

2

Answers


  1. 0

    Get the Client id for example

    $client_id
Project::where('client_id', $clientId)->where('status', 'Ongoing')->count();
    Login or Signup to reply.
  2. 0

    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();
    1. 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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search