Make consultation with distinct in Laravel

Question

I have the following table:

I need to count the amount of records where the supplier_id is equal to 2 but without repeating the column order_id. In this case it would return 1, since it has two records with 2 na supplier_id, but order_id is 1 in both records

I used the query below in mysql and returned correctly 1 record:

SELECT COUNT(DISTINCT order_id) FROM timelines WHERE supplier_id = 2;

I used the query below in Laravel and returned me 2 records

DB::table('timelines')
    ->where('supplier_id', 2)
    ->groupBy('order_id')
    ->count(); 

How could I return the correct amount which in case is 1 record?

Answer 1

Use the selectRaw in queryBuilder

$db = \DB::table('timelines')
       ->selectRaw('COUNT(DISTINCT order_id) AS total')
       ->where('supplier_id', 2)
       ->first();

var_dump($db->total);

But be careful when using, this is vulnerable to SQL Injection attacks.

The way you did, when you debug the code, you get the query

select count(*) as aggregate from `timelines` where `supplier_id` = 2 group by `order_id`;

Despite the GROUP BY return only one element, the count(*) will count all records, regardless of the GROUP BY.

If you do not want to use selectRaw, you can use this way:

$db = \DB::table('timelines')
    ->select('order_id')
    ->where('supplier_id', 2)
    ->groupBy('order_id')
    ->get(); 

var_dump( count($db) );