Level 102
Do you mean count instead of sum?
Aug 19, 2020
9
Level 1
Laravel Query Builder, I Need Help in Write Query
I have a problem can you people help me to getting out from this problem.
I have table of patients. here is table schema.
Schema::create('patients', function (Blueprint $table) {
$table->id();
$table->string('first_name');
$table->string('last_name');
$table->string('slug')->nullable();
$table->string('date_of_birth')->nullable();
$table->string('account_number');
$table->string('received_date');
$table->string('procedure_date');
$table->text('comment');
$table->string('auth');
$table->string('auth_status')->nullable();
$table->string('auth_reconsideration')->nullable();
$table->foreignId('provider_id')->constrained('providers')->onDelete('cascade')->onUpdate('cascade');
$table->timestamps();
});
and here is provider table schema:
Schema::create('providers', function (Blueprint $table) {
$table->id();
$table->string('provider_name');
$table->string('slug')->nullable();
$table->timestamps();
});
I want to write only query that can do my task.
Here the explanation of my query detail that i want to create.
task list:
1: count of patients record by provider_id
2: count of auth (auth have default value 'Yes' or 'No') where auth="Yes" by provider_id
3: count of auth status ( auth status have three default value "Approved", "Denied" or "Pending") by provider_id
Can we write query of these three task in one query?
Thanks in advance.
Level 1
@sinnbeck i want using count.
Level 56
Try this:
$records = Patient::query()
->select([
'provider_id',
DB::raw('COUNT(*) AS patients_count'),
DB::raw("COUNT(CASE WHEN auth = 'Yes' THEN 1 ELSE NULL END) AS auth_yes_count"),
DB::raw("COUNT(CASE WHEN auth = 'No' THEN 1 ELSE NULL END) AS auth_no_count"),
DB::raw("COUNT(CASE WHEN auth_status = 'Approved' THEN 1 ELSE NULL END) AS status_approved_count"),
DB::raw("COUNT(CASE WHEN auth_status = 'Denied' THEN 1 ELSE NULL END) AS status_denied_count"),
DB::raw("COUNT(CASE WHEN auth_status = 'Pending' THEN 1 ELSE NULL END) AS status_pending_count"),
])
->groupBy('provider_id')
->withCasts([
'patients_count' => 'integer',
'auth_yes_count' => 'integer',
'auth_no_count' => 'integer',
'status_approved_count' => 'integer',
'status_denied_count' => 'integer',
'status_pending_count' => 'integer',
])
->get();
You can skip the ->withCasts(...) part if you are not using Laravel 7. That would convert your columns to integer instead of strings.
If you are not using Eloquent Models, but the query builder directly, use this:
$records = DB::table('patients')
->select([
'provider_id',
DB::raw('COUNT(*) AS patients_count'),
DB::raw("COUNT(CASE WHEN auth = 'Yes' THEN 1 ELSE NULL END) AS auth_yes_count"),
DB::raw("COUNT(CASE WHEN auth = 'No' THEN 1 ELSE NULL END) AS auth_no_count"),
DB::raw("COUNT(CASE WHEN auth_status = 'Approved' THEN 1 ELSE NULL END) AS status_approved_count"),
DB::raw("COUNT(CASE WHEN auth_status = 'Denied' THEN 1 ELSE NULL END) AS status_denied_count"),
DB::raw("COUNT(CASE WHEN auth_status = 'Pending' THEN 1 ELSE NULL END) AS status_pending_count"),
])
->groupBy('provider_id')
->get();
1 like
Level 1
Thanks your answer help me but i have one more question if you don't mind dear.
How can we get count of auth_reconsideration because it have some random text that added using form when auth status is denied. Can you add this field on your written query?
Level 56
But what do you want to count? If it is random text it is hard to know what is in there.
As the field is nullable If you want to count only if the field is filled this might work:
$records = DB::table('patients')
->select([
'provider_id',
DB::raw('COUNT(*) AS patients_count'),
DB::raw("COUNT(CASE WHEN auth = 'Yes' THEN 1 ELSE NULL END) AS auth_yes_count"),
DB::raw("COUNT(CASE WHEN auth = 'No' THEN 1 ELSE NULL END) AS auth_no_count"),
DB::raw("COUNT(CASE WHEN auth_status = 'Approved' THEN 1 ELSE NULL END) AS status_approved_count"),
DB::raw("COUNT(CASE WHEN auth_status = 'Denied' THEN 1 ELSE NULL END) AS status_denied_count"),
DB::raw("COUNT(CASE WHEN auth_status = 'Pending' THEN 1 ELSE NULL END) AS status_pending_count"),
DB::raw("COUNT(auth_reconsideration) AS reconsiderations_count"),
])
->groupBy('provider_id')
->get();
As SQL's COUNT aggregate function will ignore NULL values, it would only count the rows where auth_reconsideration has some value filled in.
If you need to search for specific words you can use any SQL condition on the SQL's CASE statement:
DB::raw("COUNT(CASE
WHEN auth_reconsideration LIKE '%foo%' THEN 1
WHEN auth_reconsideration LIKE '%bar%' THEN 1
ELSE NULL
END) AS reconsiderations_count"),
The example above will count all rows where the auth_reconsideration column contains the characters "foo" or "bar".
1 like
Level 1
@rodrigo.pedra Great, Thank you so much you made my day
1 like
Level 56
You’re welcome =)
1 like
Level 1
@rodrigo.pedra @sinnbeck Hi, I need help, Can you please more elaborate with this query syntax how to find count of created_at today record.
Level 56
I am assuming you are using MySQL. Add this column to the query:
DB::raw("COUNT(CASE
WHEN CAST(created_at AS DATE) = CURRENT_DATE THEN 1
ELSE NULL
END) AS today_count"),
Take a look at MySQL date and time functions:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
Please or to participate in this conversation.