Convert Sql Query With Subquery To Laravel Query

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

Solution 1:

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.