Is it possible to convert my SQL Query to Laravel 4 Query
SQL:
SELECT
Branch_tbl.ID,
Branch_tbl.BranchName,
(
SELECT
SUM(Expenses.Expense)
FROM
Expenses
WHERE
Expenses.BranchID = Branch_tbl.ID
) as 'Total Expenses'
FROM
Branch_tbl
You may try raw expression (maybe it's not to best solution)
DB::table('branch_tbl')
->select(
'branch_tbl.id',
'branch_tbl.branchname',
DB::raw("
( select sum(expenses.expense) from expenses where
expenses.branchid = branch_tbl.id
)as 'total expenses'"))->get();
If you have a complex subquery you can separate it:
$subQuery = DB::table('expenses')
->select(DB::raw('sum(expenses.expense)'))
->whereRaw('expenses.branchid = branch_tbl.id');
DB::table('branch_tbl')
->select('branch_tbl.id','branch_tbl.branchname',
DB::raw("(" . $subQuery->toSql() . ") as 'total expenses'")
)
->get();
Be careful not to create any SQL injection with raw expression.
