Level 104
Your question is ambiguous; where is the state name stored?
May 18, 2021
6
Level 1
Count Function in Laravel PHP syntax help
I have a table users and it has list of states of a country now I want to print a table with State Name and number of times it repeats in the column to count the number of persons from that state. here is the basic code I am trying to create
<thead>
<tr class="fw-bolder fs-6 text-gray-800 px-7">
<th>Name of State</th>
<th>Number of Applicants from this state</th>
</tr>
</thead>
<tbody>
@foreach ($users as $user)
<tr>
<td>{{$user->state}}</td> //this prints the state name list as per the database entry records
<td></td>
</tr>
@endforeach
</tbody>
Level 1
state name are stored in table (users) in column state its the states of a country that I want to print a table with name of the state from the table with the number of times it is present in the table .
to finally see how many persons belong from the list of states entered respectively when they registered
Level 75
A group by with count as aggregate should work.
Level 104
You will need a sub query to join to the main query, e..g
$stateCounts = User::selectRaw('state, count(*) AS state_count')->groupBy('state');
User::selectRaw('users.*, state_counts.state_count')
->joinSub($stateCounts, 'state_counts', function ($join) {
$join->on('users.state', '=', 'state_counts.state');
})->get();
Level 1
tried and its just printing the the text instead of the values
but when using alone {{$user->state}} it prints the values of state column, that too the as many times its present in the database
Level 104
its just printing the the text instead of the values
You would need to print the value from $user->state_count separately to $user->state.
but when using along {{$user->state}} it prints the values of state column, that too the as many times its present in the database
Can you rephrase that; I don't understand
EDIT
Because of this:
@foreach ($users as $user)
I misunderstood that you wanted to output each user, with their state and the count 🤦♂️. It is better to name the variable for what it represents:
$states = User::selectRaw('state, count(*) AS state_count')->groupBy('state')->get();
@foreach($states as $state)
<td>{{$state->state}}</td>
<td>{{$state->state_count}}</td>
@endforeach
Please or to participate in this conversation.