How would I change this query to fit into Laravel's Query Builder?

Question

I've tried creating this query in Laravel and still can't get it to work with a query builder. It's giving me syntax errors yet I can run it in the tables and it run smooth.

Query trying to run:

select A.*, sum(RawAmt) as AmountOwed from (select Login, PatID,
if(length(ApptID) > 0, ApptID, VisitID) as VisitID,
ServiceDate,
TotalCharge,
InsurancePaid,
PrevPaid,
WriteOff,
Refund,
MiscDebit,
AmountOwed as RawAmt,
ApptTime,
ApptDate,
Physician,
isCopay,
HLocation from MDPay_AcctHist where Login='demo') A
group by PatID, VisitID

It's giving me a syntax issue when trying to do this with DB::raw statements in DB::select and DB::where;

Any help on trying to write this to meet laravels specs would be helpful.

Limon Monte · Accepted Answer · 2015-04-30T16:14:59.383

1

$subquery = DB::selectRaw('
        Login, PatID,
        if(length(ApptID) > 0, ApptID, VisitID) as VisitID,
        ServiceDate,
        TotalCharge,
        InsurancePaid,
        PrevPaid,
        WriteOff,
        Refund,
        MiscDebit,
        AmountOwed as RawAmt,
        ApptTime,
        ApptDate,
        Physician,
        isCopay,
        HLocation')
    ->from('MDPay_AcctHist')
    ->where('Login', '=', 'demo')
    ->toSql();

$result = DB::selectRaw('A.*, sum(RawAmt) as AmountOwed')
    ->from(DB::raw($subquery . ' as A'))
    ->groupBy('PatID', 'VisitID')
    ->get();

edited Apr 30 '15 at 16:14

answered Apr 30 '15 at 07:39

Limon Monte

52,539
45
182
213

Ok, so I dropped this code in and got this: syntax error, unexpected '}'. I tried ending the where('Login', '=', 'demo') with a ; and I got this: call_user_func_array() expects parameter 1 to be a valid callback, class 'Illuminate\Database\MySqlConnection' does not have a method 'selectRaw'. how do I need to fix this to make it work correctly? – jamadri Apr 30 '15 at 13:53
Yeah, you added the semicolon exactly where I did, look above in my comment and I'm now getting the: call_user_func_array() expects parameter 1 to be a valid callback, class 'Illuminate\Database\MySqlConnection' does not have a method 'selectRaw'. – jamadri Apr 30 '15 at 14:01
yuo, we should replace `selectRaw()` with `select(DB::raw())` here, see updated answer. – Limon Monte Apr 30 '15 at 14:14
I actually did this and now I'm running into this issue: Base table or view not found: 1051 Unknown table 'A' (SQL: select A.*, sum(RawAmt) as AmountOwed) The reason this is happening is because of this end statement not being in the query you created: ... where Login='demo') A group by PatID, VisitID How would I get the ) A group by PatID, VisitID in there? That's defining A.* at top. – jamadri Apr 30 '15 at 14:18
good question, I don't know the answer, so I separated it to subquestion: http://stackoverflow.com/q/29970481/1331425 – Limon Monte Apr 30 '15 at 14:31
Thanks for all the help, I really do appreciate it. Sometimes corporate queries are a little harder to create in Laravel, and you helping has at least wrapped my mind around how this can work. I'll follow your question and see where it ends up. – jamadri Apr 30 '15 at 14:43
You're welcome! I got the answer on our subquestion and amended this answer, take a look. – Limon Monte Apr 30 '15 at 16:16

How would I change this query to fit into Laravel's Query Builder?

1 Answers1