CODEWITHSUNDEEP
phpmysqllaravellaravel-query-builder
Yannick jansen
Yannick jansen

Reputation: 49

Laravel select db raw groupBy

I would like to display this query in laravel : 

SELECT products.*, (

SELECT COUNT(*)
FROM category
WHERE category.product_id = products.id
AND category.date_creation BETWEEN '2018-02-16 00:00:00' AND '2018-02-19 23:59:59' 
GROUP BY product_id
)nbr
FROM products

i try with this : 

    $a= $date_du;
    $b = $date_au;
    $products = DB::table('products')                                    
    ->leftjoin('category','category.product_id','=','products.id')
    ->select('products.*',
       DB::raw("(SELECT COUNT(*)  FROM category 
       WHERE category.product_id = products.id AND category.date_creation >= '$a' AND category.date_creation <= '$b' group by product_id) as nbr"))                                     
    ->get();

But clause group by doesn't work.

Upvotes: 0

Views: 3942

Answers (2)

Raj Soni
Raj Soni

Reputation: 89

You can also do this :

$products=DB::select("SELECT products.*, ( SELECT COUNT(*) FROM category WHERE category.product_id = products.id AND category.date_creation BETWEEN '2018-02-16 00:00:00' AND '2018-02-19 23:59:59' GROUP BY product_id )nbr FROM products" );

This will run a raw query and will return the desired output.

Upvotes: 1

Sagar Gautam
Sagar Gautam

Reputation: 9389

Try this one,

$products = DB::table('products')
            ->leftjoin('category','category.product_id','=','products.id')
            ->whereBetween('date_creation',[$a,$b])
            ->select(DB::raw('COUNT(*) as nbr'), 'products.*')
            ->groupBy('id')
            ->get();

This might work for you.

Upvotes: 1

Related Questions