Convert MySQL query to eloquent or query builder

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();

tisuchi

8 years ago

Level 70

Try relationship in this case. Its way easier.

3 likes

Cronix

8 years ago

Level 67

So you want to convert an already working raw sql query to eloquent, so that it can retranslate it back to raw sql (taking additional time)? Why? You already have the working query.

xmarks

8 years ago

Level 8

while this is a late reply and you might no longer require it, and my answer is not tested, I think this will work:

$query = Sales::rightJoin(DB::raw('date(sales.created_at) = calendars.datefield'))
  ->select(
    'calendars.datefield as date',
    DB::raw('IFNULL(SUM(sales.sale_total),0) AS total')
  )
  ->whereRaw(
    'calendars.datefield between (
      SELECT MIN(DATE(created_at)) FROM sales
    ) AND (SELECT MAX(DATE(created_at)'
  )->groupBy('date')->get();

Please or to participate in this conversation.