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.

Reply to

Use Markdown with GitHub-flavored code blocks.