Getting Distinct Records Laravel Using Query Builder And Using Raw Query Differs

Laravel 5.6.29 PHP 7.3.5

App\StatusAudit::where('user_id', 39)->where('new_status', 4)->whereBetween(
'created_at', [\Carbon\Carbon::parse('22-05-2019')->toDateString(), \Carbon\Carbon::parse('22-06
-2019')->toDateString()])->distinct()->count()

gives result 31, status_audits.id is different so 31, correct, but ->distinct('video_id') is giving 31 is interesting! and using groupBy giving me 1.

and

SELECT COUNT( DISTINCT status_audits.video_id) FROM status_audits WHERE user_id = 39 AND new_status = 4 AND DATE(created_at) BETWEEN "2019-05-22" AND "2019-06-22"

is giving 30

why there is difference in result 30 and 31?

Tinker result

>>> App\StatusAudit::where('user_id', 39)->where('new_status', 4)->whereBetween(
'created_at', [\Carbon\Carbon::parse('22-05-2019')->toDateString(), \Carbon\Carb
on::parse('22-06-2019')->toDateString()])->distinct('video_id')->count()
=> 31
>>> App\StatusAudit::where('user_id', 39)->where('new_status', 4)->whereBetween(
'created_at', [\Carbon\Carbon::createFromFormat('d-m-Y', '22-05-2019'), \Carbon\
Carbon::createFromFormat('d-m-Y', '22-06-2019')])->distinct('video_id')->count()
=> 28

>>> App\StatusAudit::where('user_id', 39)->where('new_status', 4)->whereBetween(
'created_at', [\Carbon\Carbon::createFromFormat('d-m-Y', '22-05-2019')->toDateSt
ring(), \Carbon\Carbon::createFromFormat('d-m-Y', '22-06-2019')->toDateString()]
)->distinct('video_id')->count()
=> 31

GroupBy result

$ php artisan tinker
Psy Shell v0.9.9 (PHP 7.3.5 — cli) by Justin Hileman
>>> App\StatusAudit::where('user_id', 39)->where('new_status', 4)->whereBetween(
'created_at', [\Carbon\Carbon::createFromFormat('d-m-Y', '22-05-2019')->toDateSt
ring(), \Carbon\Carbon::createFromFormat('d-m-Y', '22-06-2019')->toDateString()]
)->groupBy('video_id')->count()
=> 1

Update getting collection and count unique records is not efficient, but this is a workaround

>>> App\StatusAudit::where('user_id', 39)->where('new_status', 4)->whereBetween(
    'created_at', [\Carbon\Carbon::parse('22-05-2019')->toDateString(), \Carbon\Carb
    on::parse('22-06-2019')->toDateString()])->get()->unique('video_id')->count()
    => 30

Another work around is

>>> App\StatusAudit::where('user_id', 39)->where('new_status', 4)->whereBetween(
'created_at', [\Carbon\Carbon::createFromFormat('d-m-Y', '22-05-2019')->toDateSt
ring(), \Carbon\Carbon::createFromFormat('d-m-Y', '22-06-2019')->toDateString()]
)->distinct()->pluck('video_id')->count()

The distinct() method doesn't take any arguments: https://laravel.com/api/5.6/Illuminate/Database/Query/Builder.html#method_distinct. Your handwritten SQL query is counting distinct status_audits.video_ids but your query builder code is counting distinct rows.

You can pass the column name to the the count() method to count only distinct values from that column.

App\StatusAudit::where('user_id', 39)->where('new_status', 4)->whereBetween(
'created_at', [\Carbon\Carbon::parse('22-05-2019')->toDateString(), \Carbon\Carbon::parse('22-06
-2019')->toDateString()])->distinct()->count('video_id')