Consult with distinct in Laravel

Question

Consult with distinct in Laravel

Navigation

#1 by (1 votes)

0

I have the following table:

I need to count the number of records where supplier_id equals 2 but does not repeat the order_id column. In that case it would return 1, since it has two records with 2 in supplier_id , but order_id is 1 in both records

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

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

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

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

How could I do to return the correct quantity which in the case is 1 record?

php laravel

asked by anonymous 05.01.2018 / 03:32

1 answer

How to make the lightbox appear in front of everything? JavaScript property definition error

score 1 · Accepted Answer

Use 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 it, this is vulnerable to SQL Injection attacks.

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

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

Although GROUP BY returns only one element, count(*) will count all records, regardless of 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) );