Objection.js: Order of joins

[Misticek]

Hi, I am struggling with order of joins. And order of joins is important for me in some queries.

I have this kind of query:

TopicModel
.query()
.joinEager('[user, workspace, topic]')
.joinRaw('LEFT JOIN SOME_TABLE')

I am expecting to have that LEFT JOIN SOME_TABLE AFTER the eager joins in final SQL query. But Objection is putting the second joinRaw as first join in final SQL query, like this:

LEFT JOIN SOME_TABLE -- this should be last!
LEFT JOIN "users" AS "user" ON "user"."id" = "ut"."user_id"
LEFT JOIN "workspaces" AS "workspace" ON "workspace"."id" = "ut"."workspace_id"
LEFT JOIN "topics" AS "topic" ON "topic"."id" = "ut"."topic_id"

Is it possible somehow to change the order of joins?

Thanks in advance!
Martin

[koskimas]

Do you need to eager-load the nested tree structure of objects, or do you simply need the joins? If you only need the joins, you could use joinRelation instead of joinEager and the order should be preserved

TopicModel
.query()
.joinRelation('[user, workspace, topic]')
.joinRaw('LEFT JOIN SOME_TABLE')

In case of joinEager the joins are added as the last item and there is no easy way around it. If you really need joinEager, you can use this as a workaround:

TopicModel
.query()
.joinEager('[user, workspace, topic]')
.onBuild(builder => builder.joinRaw('LEFT JOIN SOME_TABLE'))

[Misticek]

Thanks for this quick reply! 💯

I have tried runBefore, onBuild, even onBuildKnex. But in every case that LEFT JOIN SOME_TABLE is still the first join in final SQL query and Eager joins are always last. So this did not help.

Anyway, I need that eager because I need fully hydrated user, workspace and topic models in the result set. So simple joinRelation is not sufficient here. Or is there any other way how to hydrate user, workspace and topic than using eager loading? Still trying to find some proper objection solution to avoid raw SQL query creation.

[koskimas]

Sure, there's a bunch of things you could do. Could you post the whole query here so that I know the details?

[Misticek]

That whole query is quite simple. There is just really lot of WHERE conditions that do not need to be mentioned here.

const query = TopicEnrolmentModelAsEditor.query()
      .alias('ut')
      .joinEager('[user as u, workspace as w, topic as t]')
      .joinRaw('LEFT JOIN LATERAL (SELECT COALESCE (
                      (SELECT title
                       FROM topic_versions AS tv
                       WHERE tv.id = "topic".production_topic_version_id
                       LIMIT 1),
                      (SELECT title
                       FROM topic_versions AS tv
                       WHERE tv.topic_id = "topic".id
                         AND status = 'DRAFT'
                       LIMIT 1), '') AS "currentTitle") "lateralResource" ON TRUE')
      .whereNotNull('t.production_topic_version_id')
      .where('u.organisation_id', this.ctx.organisation_id)

I am expecting to have array of TopicEnrolmentModelAsEditor instances in the result set, where every TopicEnrolmentModelAsEditor has already instance of user, workspace and topic. And that of course works. The problem is adding the joinRaw with the LATERAL usage. Because that LATERAL query is using id from topic (see tv.topic_id = "topic".id), it must be stated AFTER these eager joins in final SQL query. If there is any any way how to hydrate user, workspace and topic other than Eager loading, that might be helpful. Otherwise I think I need to rebuild this query from scratch somehow.

[koskimas]

What's topic in that query? Do you mean ut? or t?

[koskimas]

If topic is a BelongsToOne relation or a HasOneRelation, could you simply do this?

const query = TopicEnrolmentModelAsEditor.query()
  .alias('ut')
  .joinRelation('topic as t')
  .eager('[user as u, workspace as w, topic as t]')
  .joinRaw(
    `LEFT JOIN LATERAL (
      SELECT COALESCE(
        (SELECT title FROM topic_versions AS tv WHERE tv.id = "t".production_topic_version_id LIMIT 1),
        (SELECT title FROM topic_versions AS tv WHERE tv.topic_id = "t".id AND status = 'DRAFT'LIMIT 1), 
        ''
      ) AS "currentTitle"
    ) "lateralResource" ON TRUE`
  )
  .whereNotNull('t.production_topic_version_id')
  .where('u.organisation_id', this.ctx.organisation_id);

[koskimas]

Have you noticed that if you use aliases in eager expressions, the properties in the parent object are aliased too? So you probably want to remove the aliases from eager.

[koskimas]

You can also use subqueries to clean up the raw join if you prefer

const query = TopicEnrolmentModelAsEditor.query()
  .alias('ut')
  .joinRelation('[user as u, topic as t]')
  .eager('[user, workspace, topic]')
  .joinRaw(
    `LEFT JOIN LATERAL (
      SELECT COALESCE(?, ?, '') AS "currentTitle"
    ) "lateralResource" ON TRUE`,

    [
      TopicVersionModel.query()
        .alias('tv')
        .whereColumn('tv.id', 't.production_topic_version_id')
        .limit(1),

      TopicVersionModel.query()
        .alias('tv')
        .whereColumn('tv.topic_id', 't.id')
        .where('status', 'DRAFT')
        .limit(1)
    ]
  )
  .whereNotNull('t.production_topic_version_id')
  .where('u.organisation_id', this.ctx.organisation_id);

[Misticek]

Oh my fluffy cat.. Man, you are genius. This approach is great! Combining joinRelation and eager is very interesting.
It returns what I need and correctly hydrating user, workspace and topic objects.
The only drawback here is the shape of final SQL query which is not so important now because it works! It's just joining topics twice but that's not so big deal right now as the query is returning what I need.

Drawback, simplified query:

SELECT -- ALL --
FROM "user_topics" AS "ut"
INNER JOIN "topics" AS "t" ON "t"."id" = "ut"."topic_id" -- FIRST topics JOIN
LEFT JOIN LATERAL (-- SELECT --) "lateralResource" ON TRUE
LEFT JOIN "users" AS "u" ON "u"."id" = "ut"."user_id"
LEFT JOIN "workspaces" AS "w" ON "w"."id" = "ut"."workspace_id"
LEFT JOIN "topics" AS "topic" ON "topic"."id" = "ut"."topic_id" -- SECOND topics JOIN

Objection builder:

const query = TopicEnrolmentModelAsEditor.query()
      .alias('ut')
      .joinRelation('[topic as t]')
      .joinEager('[user as u, workspace as w, topic]')
      .joinRaw( `LEFT JOIN LATERAL (
        SELECT COALESCE(?, ?, '') AS "currentTitle"
        ) "lateralResource" ON TRUE`,
        [
          TopicVersionModel.query()
            .alias('tv')
            .select('title')
            .whereColumn('tv.id', 't.production_topic_version_id')
            .limit(1),

          TopicVersionModel.query()
            .alias('tv')
            .select('title')
            .whereColumn('tv.topic_id', 't.id')
            .where('status', 'DRAFT')
            .limit(1)
        ])
      .whereNotNull('t.production_topic_version_id')
      .where('u.organisation_id', this.ctx.organisation_id)
      .select('lateralResource.currentTitle as topic:currentTitle') //hydrating topic with custom attribute
      .select('ut.*') //need to load all columns to be properly hydrated
      .select('t.*') //need to load all columns to be properly hydrated

[koskimas]

In my example, I didin't use joinEager. I used the normal eager, which doesn't generate joins. It simply adds separate queries. They are suuuper fast though and you don't need to worry about performance.

Yeah, the objection query is alot more complex in this case than raw SQL.

[koskimas]

If you use the normal eager, you don't need to play with the selects either.

[koskimas]

I think you could simplify this even more by not using the lateral join:

const query = TopicEnrolmentModelAsEditor.query()
  .alias('ut')
  .joinRelation('[user as u, topic as t]')
  .eager('[user, workspace, topic]')
  .whereNotNull('t.production_topic_version_id')
  .where('u.organisation_id', this.ctx.organisation_id)
  .select(
    'ut.*',
    TopicVersionModel.query()
      .alias('tv')
      .select('title')
      .whereColumn('tv.id', 't.production_topic_version_id')
      .orWhere(query => query.whereColumn('tv.topic_id', 't.id').where('status', 'DRAFT'))
      .limit(1)
      .as('currentTitle')
  );

[Misticek]

You are right in every aspect and the last query is total winner.
Where is some donate button?! You totally deserve it!
So from my perspective it seems like you can control order of joins by combining of joinRelation and eager calls. Good to know next time.
This was extremely helpful, thank you!