1 year ago

Convert MySQL query to eloquent or query builder

Posted 1 year ago by Warar

MySQL query

select calendars.datefield AS date, ifnull(sum(sales.sale_total),0) as total from sales right join calendars on (date(sales.created_at) = calendars.datefield) where (calendars.datefield between (select min(date(created_at)) from sales) and (select max(date(created_at)) from sales)) group by date

I'm trying like this...

DB::table('sales') ->rightJoin('calendars', 'sales.sale_date', '=', 'calendars.datefield') ->select(DB::raw('calendars.datefield as date, ifnull(sum(sales.sale_total),0) as total')) ->whereBetween('calendars.datefield', ['MIN(created_at)', 'MAX(created_at)']) ->groupBy('date') ->get();

Please sign in or create an account to participate in this conversation.