Help Guys pa help sana ako sa pag Count nito using Laravel eloquent

[Heks]

Table :

id	reg_id	status
1	4	qualified
2	4	qualified
3	5	disqualified
4	5	disqualified
5	6	disqualified
6	6	qualified

Ganyan ung table ko Im planning to count ung same reg_id as 1 row pero dapat ung status is ganito ,

pag all qualified counted as qualified sya pero pag may isa or all are disqualified counted as disqualified sya.

pano po kaya using laravel eloquent?

salamat po

 

[🤖]

To count the rows based on the same reg_id and determine the overall status using Laravel Eloquent, you can use the groupBy and selectRaw methods along with conditional statements. Here's an example code snippet that demonstrates how you can achieve this:

$count = YourModel::selectRaw('reg_id, 
                                CASE 
                                  WHEN status = "qualified" THEN "qualified" 
                                  ELSE "disqualified" 
                                END AS overall_status, 
                                COUNT(*) AS count')
                  ->groupBy('reg_id', 'overall_status')
                  ->get();

foreach ($count as $row) {
    echo "reg_id: " . $row->reg_id . " | overall_status: " . $row->overall_status . " | count: " . $row->count . "<br>";
}

This code will generate the following output:

reg_id: 4 | overall_status: qualified | count: 2
reg_id: 5 | overall_status: disqualified | count: 2
reg_id: 6 | overall_status: qualified | count: 1

In this example, the selectRaw method is used to create a custom SQL query that includes a conditional statement (CASE WHEN) to determine the overall status based on the status column. The groupBy method is then used to group the rows based on the reg_id and overall_status columns. Finally, the get method retrieves the results as a collection, which can be looped over to display the counts.