Objection.js: Handle multiple joinRelations that share identical RelationExpression

[johnmaia]

Given the following dummy entities:

father: {
  id,
  name,
  ...
}

mother: {
  id,
  name,
  ...
}

student: {
 id,
 fatherId,
 motherId,
 classId
 ...
}

class: {
 id,
 ...
}

student entity has two relations:

father: {
  join: {
    from: 'student.fatherId',
    to: 'father.id'
  },
  modelClass: Father,
  relation: BelongsToOneRelation
},
mother: {
  join: {
    from: 'student.motherId',
    to: 'mother.id'
  },
  modelClass: Mother,
  relation: BelongsToOneRelation
}

class entity has one relation:

student: {
  join: {
    from: 'student.classId',
    to: 'class.id'
  },
  modelClass: Student,
  relation: HasManyRelation
}

If I add two new named filters on the class model:

• fatherName: filter classes that have students with fathers with a given name

qb.joinRelation('student.father').where('name', 'ilike', `%${value}%`)

• motherName: filter classes classes that have students with mothers with a given name

qb.joinRelation('student.mother').where('name', 'ilike', `%${value}%`)

And if I try to use the two filters at the same time I receive the following error:

error: table name "student" specified more than once

I'm thinking on developing an util that will receive an instance of the QueryBuilder and a RelationExpression and check if the relation is already set. Set it if its a new relation and simply return the qb instance if its already defined.

Then the code of the filters would be something like this:

• fatherName:

relationUtil(qb, 'student.father').where('name', 'ilike', `%${value}%`)

• motherName:

relationUtil(qb, 'student.mother').where('name', 'ilike', `%${value}%`)

Is there any other way to work around this issue? If this is the only way, whats the best procedure to check if a relation is already defined on the QueryBuilder instance (checking paths like this: qb._operations[0].expression doesn't look like a great way).

[koskimas]

This should work:

qb.joinRelation('student.[father, mother]')

Also the relation expressions can be objects. So in this case:

qb.joinRelation({
  student: {
    father: true,
    mother: true
  }
})

An object like that can be easily merged from multiple individual expressions using for example lodash merge function.

[koskimas]

It would be cool if objection would detect that a join is already added and wouldn't add it again. Currently it's pretty difficult to implement internally.

[johnmaia]

The issue here is that the filters are defined in a generic form where I can use one or two of them and the joinRelation is defined within each filter. If I wish to use two filters that hold joinRelations with similar RelationExpression I get the error mentioned above.

If objection was able to hand duplicated joins it would be awesome. But given that this isn't already implemented, the only option now is to develop the util that I've mention.

What's the best way for me to check the defined joins on QueryBuilder instance? Check this array: _operations?

[koskimas]

@johnmaia There is no way to do that using the public API. You'd need to resort to the private API and go through _operations etc. I'd advice against that since the internal structure can change at any given moment.

Even if you do use the private API, I'm not sure you can do that since the joins are often built by joining a subquery, and that subquery is a knex query. You need to also dig through knex query builder's internals to get what you want. That's basically why it's difficult to implement for me too. Knex doesn't really have any kind of introspection/reflection API.

[koskimas]

Actually you could create a "good enough" solution without touching the private API by overriding the query builder's joinRelation method.

[koskimas]

Or something along these lines:

class CustomQueryBuilder extends Model.QueryBuilder {
  constructor(...args) {
    super(...args);
    this._joins = [];
  }

  customJoinRelation(relExpr) {
    this._joins.push(relExpr);
    return this;
  }

  execute(...args) {
    this.joinRelation(combineExpressions(this._joins));
    return super.execute(...args);
  }
}

class BaseModel extends Model {
  static get QueryBuilder() {
    return CustomQueryBuilder;
  }
}

[johnmaia]

Thanks for the help @koskimas!
I'll go ahead and develop a CustomQueryBuilder like you suggested.

[johnmaia]

Hey there @koskimas!

So we went forward and developed our own CustomQueryBuilder, similar to what you've suggested, but we've reached into a limitation.

Using the structure that I've mentioned above, if we use a subquery in a named filter on the Class model:

qb.whereIn('id', Student
  .query()
  .select('classId')
  .joinRelation('father')
  .where('name', 'waldo')
)

The SQL will look something like this:

select "app"."class".* 
from "app"."class" 
where "id" in (
  select "classId" 
  from "app"."student" 
  where "father"."name" = ($1)
)

And we receive the following error:

missing FROM-clause entry for table "father"

As you can see, its missing the necessary inner join which makes sense given that our CustomQueryBuilder will only actually join the given relations when the execute method is called. And since its never called, the subQuery qb won't have the necessary relations when it's "merged" into the upper level qb.

What would you suggest to do in this situation?

Note that the example is just that. I know that that particular query could be performed without using a subquery, but in our case, the use of a subquery is mandatory.

[koskimas]

@johnmaia Sorry for the huge delay. I'm working on merging these joins out of the box. You should see something you can test today or tomorrow.

[johnmaia]

@koskimas no problem. :relaxed:
Looking forward to the next objection release!

[koskimas]

This should be fixed now. I'll release a new release candidate version. You can soon test it out by running npm install objection@next