Error converting my raw sql into eloquent code

Question

Hello fellow developers, I'm trying to convert my query into eloquent code,but as I was using "CASE" commands among other commands I decided to insert the select section into a DB::raw but now I experience some syntax error or access violation:1055, here is my sql:

SELECT pa.id_budget_total,pa.name,CASE WHEN co.budget IS null THEN pa.amount ELSE pa.amount - SUM(co.budget) END AS left 
    FROM budget_total pa LEFT JOIN convening co ON pa.id_budget_total = co.budget_total 
    where pa.status = 1 GROUP BY pa.name

I kind of converted it to eloquent but when I add co.budget it tells me the error code followed by that co.budget is not group by, and yes it displays no error if I add it within the groupBy but that'd change the whole result. Here it's the eloquent code:

budget_total::select(DB::raw("pa.id_budget_total,pa.name, CASE WHEN co.budget IS null THEN pa.amount ELSE pa.amount - SUM(co.budget) END AS restante"))
->from("budget_total as pa")->leftJoin("convocatoria as co","pa.id_budget_total","=","co.id_budget_total")->where("pa.status", 1)
->groupBy("pa.id_budget_total","pa.name","pa.amount")->get();

I'm getting all the related convening-budgets and substracting them from the total budgets but in case that there's no convening budgets related to a total budget I just print the total budget(in order to don't get a NULL value) — Davb
– Davb, Commented Oct 2, 2018 at 0:20

Tim Biegeleisen · Accepted Answer · 2018-10-02 00:35:20Z

1

One option would be to always use the ELSE condition, but then COALESCE a NULL sum to zero.

budget_total::select(DB::raw("pa.id_budget_total, pa.name,
    pa.amount - COALESCE(SUM(co.budget), 0) AS restante"))
    ->from("budget_total as pa")
    ->leftJoin("convocatoria as co", "pa.id_budget_total", "=", "co.id_budget_total")
    ->where("pa.status", 1)
    ->groupBy("pa.id_budget_total", "pa.name", "pa.amount")
    ->get();

edited Oct 2, 2018 at 0:35

answered Oct 1, 2018 at 23:49

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Davb Over a year ago

Thank you so much @Tim it works perfectly! I really appreciate it!

Davb Over a year ago

well I just had to delete the "END" word before AS restante to get it working

Collectives™ on Stack Overflow

Error converting my raw sql into eloquent code

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related