sql - 我如何将 SQL 原始查询重写为 Laravel 查询生成器

Question

我有完美的 postgresql 查询

select "exhibitions_artworks".*, "curator_rating"."curator_id", "curator_rating"."selected",
       "curator_rating"."rating", "curator_rating"."submitted" from "exhibitions_artworks"
full outer join "curator_rating" on "curator_rating"."artwork_id" = "exhibitions_artworks"."id"
where "exhibitions_artworks"."exhibition_id" = 15
  and "exhibitions_artworks"."exhibition_id" is not null
  and "active" = true
  and "exhibitions_artworks"."status" = 0
  and "curator_rating"."curator_id" = 71 or "curator_rating"."curator_id" is null

我使用 laravel，我想将其重写为 Laravel 查询生成器。但是 Laravel ORM 不支持full outer join. 有任何想法吗？

标签： sqllaravelpostgresqljoineloquent

Answer 1

在 Laravel 上，如果你想编写原始 SQL，你可以使用DB:raw.

例子：

$results = DB::select( DB::raw("SELECT * FROM table WHERE column = '$variable'") );

DB::raw()用于生成查询生成器不会进一步解析的任意 SQL 命令。

---

（更新）

使用以下 SQL 作为示例：

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;

我们也可以使用 UNION 来处理相同的结果：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id

在 Laravel 框架中，你可以使用 unionAll 方法：

$first = DB::table('users')
            ->whereNull('first_name');

$users = DB::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

参考：https ://laravel.com/docs/7.x/queries#unions