将带有子查询的SQL查询转换为Laravel查询 [英] Convert SQL Query with Subquery to Laravel query

问题描述

是否可以将我的SQL查询转换为Laravel 4查询

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();

请注意不要使用原始表达式创建任何SQL注入.

Be careful not to create any SQL injection with raw expression.

这篇关于将带有子查询的SQL查询转换为Laravel查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！