php - 如何使用 eloquent 选择带有左连接的子查询？

Question

我对 Laravel 很陌生，如何使用 Eloquent 模型在 Laravel 中进行此查询:

SELECT
(
  SELECT departments.deptname FROM school.counts
  LEFT JOIN reference.departments
  ON counts.departmentcode = department.departmentcode
  WHERE counts.countid = a.countsid
) AS departmentDesc
FROM school.subjecthdr a
LEFT JOIN school.subjectdtls b
ON a.subjectid = b.subjectid;

我不想使用原始查询，有什么办法吗？如果有的话，我仍然很欣赏原始查询建议。

Answer 1

我认为您仍然需要一些原始查询。

$dept_desc = \DB::table('school.counts')
->leftjoin('reference.departments', 'counts.departmentcode', '=', 'departments.departmentcode')
->whereRaw('counts.countid = a.countsid')
->selectRaw('departments.deptname');


\DB::table('school.subjecthdr AS a')
->leftjoin('subjectdtls AS b', 'a.subjectid', '=', 'b.subjectid')
->selectRaw('(' . $dept_desc->toSql() . ') AS departmentDesc')
->get();

PS:我认为您leftjoin subjectdtls b，但似乎您不需要它。

并且您不仅使用一个数据库，如果您想使用Eloquent\Builder，你需要做这样的事情: How to use multiple databases in Laravel