I have this raw query:
collect(DB::select("SELECT month, SUM(amount) AS amount from (SELECT MONTH(spent_at) AS `month`, SUM(amount) as `amount` FROM expenditures GROUP BY MONTH(spent_at)
UNION ALL
SELECT MONTH(paid_at) AS `month`, SUM(total) as `amount` FROM salaries GROUP BY MONTH(paid_at)) as costs GROUP BY month"))->pluck('amount', 'month')
and it works fine but if I convert it to eloquent like this:
$salaries = Salary::select(DB::raw('SUM(total) as amount'), DB::raw('MONTH(paid_at) as month'))
->whereRaw('YEAR(paid_at) = YEAR(NOW())')
->groupBy('month');
Expenditure::select(DB::raw('SUM(amount) as amount'), DB::raw('MONTH(spent_at) as month'))
->whereRaw('YEAR(spent_at) = YEAR(NOW())')
->unionAll($salaries)
->groupBy('month')
->pluck('amount', 'month');
it is not giving me the correct result, Can you help me with this?
You can get the compiled SQL by adding ->toSql() after ->groupBy(). Then log the result and see how it differs from your raw query.
$sql = Expenditure::select(...)
...
->groupBy('month')
->toSql();
logger($sql);
You can also just call ->dd() to end execution and print the query if this isn't some background task.
I would probably push the query into a database view and the query that.
https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application
Please or to participate in this conversation.