SQL to Eloquent -- How to convert this query?

Posted 1 year ago by sergionader

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!!

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

Laracasts Mascot

Hi, Have We Met Yet?

Did you know that, in addition to the forum, Laracasts includes well over 1000 lessons on modern web development? All for the price of one lunch out per month.

Sign Me Up

Channels

Reply to

Use Markdown with GitHub-flavored code blocks.