如何将SQL原始查询重写为

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不支持full outer join。有什么想法吗？

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;

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

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