Objection.js: relatedQuery will not provide an alias for a self-referring relation

[besuhoff]

First of all I'd like to express my greatest appreciation for the work you do as objection.js is in my opinion the best Node ORM and is maintained with the highest quality and the shortest reaction time one could expect. Thank you very much!

Now, the issue I'm trying to deal with (might be a user error):

class Category extends Model {
  static get tableName() {
    return 'category';
  }

  static get relationMappings() {
    return {
      parent: {
        relation: Model.BelongsToOneRelation,
        modelClass: Category,
        join: {
          from: 'category.parentId',
          to: 'category.id',
        },
      },
      children: {
        relation: Model.HasManyRelation,
        modelClass: Category,
        join: {
          from: 'category.id',
          to: 'category.parentId',
        },
      },
    };
  }
}

Category.query
  .select('category.*', Category.relatedQuery('children').select(Category.raw('count(*) = 0')).as('isLinked'));

Here I'm trying to select the Boolean of if a category has children into a specific field.

That would compile to the following:

  sql: 'select `category`.*, (select count(*) = 0 from `category` where `category`.`parentId` = `category`.`id`) as `isLinked` from `category`' }

— which would not work because related table has the same name (obviously), and that would override the outer query. Shouldn't it be considered on a relation that if the table names in from and to are equal, then we should add a pseudo name to it?

[koskimas]

Thank you!

Does this work?

Category.query
  .select([
    'category.*', 
    Category.relatedQuery('children').alias('c').select(Category.raw('count(*) = 0')).as('isLinked')
  ]);

There's no easy way to do this automatically, because knex (the underlying query builder) has no public reflection API. In other words, there is no easy way to detect these situations.

[besuhoff]

@koskimas thought about it as well just now, but unfortunately alias changes both fields, so

where `c`.`id` = `c`.`parentId`

it is then.

So probably the best that can be done here is documenting that self-related queries will not work throught relatedQuery properly

[besuhoff]

I can think of a patch to relatedQuery that would check for if relationMappings.relation.from and relationMappings.relation.to table names are equal and then did something like this.query().alias('relation').where(...)

[koskimas]

This is a bug and I'd like to fix it, but every fix I can think of would break backwards compatibility, meaning the fix could only be released with the next major version (2.0).

[koskimas]

@besuhoff I decided to fix this even though it may break some code somewhere. I think this feature should have been written like this in the first place, and the change can be thought of as a bugfix. I feel like relatedQuery is broken without this and I don't want to wait for 2.0.

The fix is to use the relation's name as the subquery alias. If that collides for some reason, alias method can now be used too and it works as it should. Could you test the master branch to see if the fix works in your case?

[besuhoff]

@koskimas cool, thank you, will do later today and report

[besuhoff]

@koskimas master worked like a charm for me, sorry for such a delay with the response.