Objection js sub query when using Eager methods

Question

Does any body knows why ObjectionJs makes a sub query when using Eager methods?

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

The sql query is

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

instead of

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

Does this cause any performance issue?

Answer 1

This question was answered at least here and there has been discussion about it every now and then on the gitter support channel https://github.com/Vincit/objection.js/issues/301

Basically it shouldn't be a problem unless you are using mysql which is older than 5.7. So for mssql, oracle, postgres and mysql > 5.7 the performance is the same for both queries.

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.