SQL to Eloquent -- How to convert this query?

Hello!

I have this query that works very well:

$sql = DB::select("
        SELECT
            DATE_FORMAT(visits.dt, '%m') AS month_idx,
            DATE_FORMAT(visits.dt, '%b') AS month,
            DATE_FORMAT(visits.dt, '%Y') AS year,
            ROUND(sum(product_visit.amount)) AS y
        FROM    visits, product_visit
        WHERE
            visits.id       = product_visit.visit_id
            AND product_visit.amount>0
            AND (visits.dt between '$dt_start' and '$dt_start')
        GROUP BY month_idx, month, year
        ORDER BY year desc, month_idx desc;
        ");

However, the following one will not run:

 $sql = DB::table('visits')
        ->join('product_visit', 'visit_id', '=', 'visits.id')
        ->where('product_visit.amount', '>', 0)
        ->whereBetween('visits.dt', [$dt_start, $dt_start])
        ->groupby('month_idx', 'year')
        ->groupby(['year', 'desc'])
        ->select(
            'DATE_FORMAT(visits.dt, \'%m\') AS month_idx',
            'DATE_FORMAT(visits.dt, \'%b\') AS month',
            'DATE_FORMAT(visits.dt, \'%Y\') AS year',
            'ROUND(sum(product_visit.amount)) AS y'
        )
        ->get();

The error message is:

There was an error: {"readyState":4,"responseText":"{\n "message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'DATE_FORMAT(visits.dt, '%m')' in 'field list' (SQL: select DATE_FORMAT(visits.dt, '%m') as month_idx, DATE_FORMAT(visits.dt, '%b') as month, DATE_FORMAT(visits.dt, '%Y') as year, ROUND(sum(product_visit.amount)) as y from visits inner join product_visit on visit_id = visits.id where product_visit.amount > 0 and visits.dt between 2017-01-01 and 2017-01-01 group by month_idx, year, year, desc)"

Any idea of how we can make it work?

Thanks!!

Dry7

8 years ago

Level 36

@sergionader use selectRaw

$sql = DB::table('visits')
        ->join('product_visit', 'visit_id', '=', 'visits.id')
        ->where('product_visit.amount', '>', 0)
        ->whereBetween('visits.dt', [$dt_start, $dt_start])
        ->groupby('month_idx', 'year')
        ->groupby(['year', 'desc'])
        ->selectRaw(
            'DATE_FORMAT(visits.dt, \'%m\') AS month_idx',
            'DATE_FORMAT(visits.dt, \'%b\') AS month',
            'DATE_FORMAT(visits.dt, \'%Y\') AS year',
            'ROUND(sum(product_visit.amount)) AS y'
        )
        ->get();

1 like

sergionader

8 years ago

Level 3

Thanks a lot, @Dry7

For the record, I had to make a little change to make it work.

        $sql = DB::table('visits')
        ->join('product_visit', 'visit_id', '=', 'visits.id')
        ->where('product_visit.amount', '>', 0)
        ->whereBetween('visits.dt', [$dt_start, $dt_start])
        ->groupBy('month_idx', 'year')
        ->orderBy('year', 'desc')
        ->selectRaw('DATE_FORMAT(visits.dt, \'%m\') AS month_idx')
        ->selectRaw('DATE_FORMAT(visits.dt, \'%Y\') AS year')
        ->selectRaw('ROUND(sum(product_visit.amount)) AS y')
        ->get();

if I try

...
 ->selectRaw(
            'DATE_FORMAT(visits.dt, \'%m\') AS month_idx',
            'DATE_FORMAT(visits.dt, \'%b\') AS month',
            'DATE_FORMAT(visits.dt, \'%Y\') AS year',
            'ROUND(sum(product_visit.amount)) AS y'
        )
...

I get an error saying it expects an array.

The way I did, it's now working.

Please or to participate in this conversation.