Add a way to modify the "on clause" when using joinRelated.

[koskimas]

Currently this is only possible using the "raw" join methods inherited from knex.

[Zeswen]

Have you considered adding support for this in 3.0.0? It would be awesome, I assume it would be extending the knex .on method to objection's QueryBuilder :)

[goodeath]

@koskimas That would be a possible future feature? Today I did need this function, so, somehow, I'd like to contribute too.

[goodeath]

Well, I tried some workarounds, and got this:

Model.query()
  .withGraphFetched(relation_name)
  .modifyGraph(relation_name, (builder) => {...builder modifications...})

My scenario is:

Table A:

• id
• data

Table B:

• id
• A_id
• data
• is_deleted

I wanted to get Table A records always, and retrieve Table B records if is_deleted column is equal to false. So, as withGraphFetched make different queries per relation (maybe a performance issue could happen here), the where clauses is inserted per query. In this specific case, it works like an "left join on clause condition"

While I was writing this comment, I did some tests and I found out the following:

Model.query()
  .wighGraphJoined(other_relation)
  .withGraphFetched(relation_name)
  .modifyGraph(relation_name, (builder) => {...builder modifications...})

In this way, the generate SQL looks like:

SELECT ..fields..
FROM table
LEFT JOIN
	(SELECT fields
		FROM relation_table
		WHERE "is_deleted" = false
		ORDER BY ...) AS relation_name ON relation_condition
...OTHER JOINS...
...WHERE... 

Still a sub-query, but, it's done inside database and we still be able to filter records before, so it's a little bit better. The combination of withGraphFetched and withGraphJoined producs this result. In this way, the generate SQL looks like:I hope that it

[caschbre]

I think there are a couple ways to tackle this...

ModelA.query()
  .withGraphFetched('relationB')
  .modifyGraph('relationB', qb => qb.where('is_deleted', false));

So that looks similar to what you have. The docs to mention that withGraphFetched uses sub-queries where withGraphJoined attempts to make a single query. There should be negligible performance differences between the two if indexes are properly setup.

You could also make a second relationship with the modifier built in.

{
  relationB: {
    ...
  },
  relationB_false: {
    ... (same as relationB)
    modify: qb => qb.where('is_deleted', false),
  },
}

The above doesn't really change the queries... but it pre-defines the relationship to tableB where you only want is_deleted = false.