时间戳

首页 > 解决方案 > 使用Eager方法时反对js子查询

sql - 使用Eager方法时反对js子查询

问题描述

有谁知道为什么 ObjectionJs 在使用 Eager 方法时会进行子查询？

ModelA.query().withGraphJoined('tableB')

sql查询是

select "tableA"."id" as "id" "tableA" left join (select "tableB". from "tableB") as "tableB" on "tableB"."id" = "tableA"."fkB"

代替

select "tableA"."id" as "id" "tableA" left join tableB

这会导致任何性能问题吗？

标签： sqlnode.jsperformanceormobjection.js

解决方案

这个问题至少在这里得到了回答，并且在 gitter 支持频道https://github.com/Vincit/objection.js/issues/301上时不时地对此进行了讨论

除非您使用的是 5.7 之前的 mysql，否则基本上这应该不是问题。因此，对于 mssql、oracle、postgres 和 mysql > 5.7，这两个查询的性能是相同的。

The problem is the modifyEager function that needs to work for the eagered relations. The user is allowed to call any of the where and select methods knex has in the modifier
function. These function calls need to be applied so that they filter the relation. The easiest way was to join a subquery and apply the wheres and selects on the subquery.

We could map the wheres into on statements for the join, but there are no on equivalent for most of the where methods in the knex join builder. Also we need to solve the select thing somehow. It's a bit tricky since in addition to whatever the columns the user selects for the relation, we need to select all the "join columns" so that we can "unflatten" the result into a tree structure.

So it's not just the missing methods, but also a bunch of other stuff.

推荐阅读