Objection.js: Table alias doesn't work with `joinRelation`

[koskimas]

joinRelation doesn't use the table alias if specified like this:

Person
  .query()
  .from('Person as p')
  .joinRelation('pets')

The full table name is used instead.

[koskimas]

Fixing this will be tricky and hacky since there is no way to ask knex query builder if it has an alias set. We need to parse the string given to from (or its aliases) ourselves and then somehow pass this information to all the places that need it.

[jeff-kilbride]

Honestly, I think using .from() to setup an alias is hacky, anyway. Is there a better way to do it? Would it be easier to have something like:

Person({alias: 'p'})
  .query()

or

  .query({alias: 'p'})

Even something in the model class, like tableAlias to go along with tableName. I typically use the same aliases in all my queries.

[zuck]

@jeff-kilbride's idea sounds very good to me!

[koskimas]

Person({alias: 'p'})
  .query()

cannot be used since Person is a constructor. It would be techically possible with ES5 but totally impossible with classes defined with class keyword.

Person
  .query({alias: 'p'})

This would be a good syntax, but the query method already accepts a single agument: the knex connection or transaction. We could simply detect what was passed in, but wouldn't that be confusing?

[jeff-kilbride]

Would it be easier to have something in the model?

static get tableAlias() {
    return 'p';
}

or even

static get tableName() {
    return 'person as p';
}

I'm not sure how others would use it, but I typically use the same aliases for my tables across queries. Or even something closer to the way we can specify aliases in the EagerOptions, right now -- TableOptions?

[einnjo]

I'm piggybacking on this issue as this seems related to something I'm currently dealing with.

I have a model with both a table and a view. I want to be able to switch easily between querying the view and the model.

class Event extends BaseModel {

    static get tableName () {
        return 'events';
    }
// ...

// Query the events table.
Event.query().where('startDate'...);

// Query only live events through `live_events` view.
Event.query().from('live_events').where('startDate'...);

The problem, as you said, is that this breaks other query methods that fail to take the new tableName into account.

[debone]

I'm facing the same issue and not having success solving it, specifically trying to eager load a relation where the from( ) is a aliased name. Does anybody have a light on how to bypass this without going raw?

[koskimas]

The problem with eager loading is that relationMappings have the source and target tables hardcoded in them. Changing a table name for an eager query would require recreating all relations of all models that mention the changed table. It would require quite a bit of refactoring to make this work.

[debone]

Thanks for the answer. I assume then that the proposed solutions for "tableName" wouldn't be feasible so, or would it? Because the hard-coded string now would be returning the aliased name.

[koskimas]

I did the refactoring anyway. I feel like it made the codebase cleaner by adding an abstraction layer for resolving table names and aliases. There is a branch called views-and-aliases that has a beta version of this. You can test it by putting this

"objection": git://github.com/Vincit/objection.js.git#156cf62

in your package.json.

I only implemented this for table method for now. Let's figure out the best syntax later if this turns out to solve your problems. So things like these should now work:

Person
  .query()
  .table('someView')
  .joinRelation('whatever')
  .eager('blaaBlaaBlaa')
  .where('someView.id', foo)

Person
  .query()
  .table('Person as p')
  .joinRelation('whatever')
  .eager('blaaBlaaBlaa')
  .where('p.id', foo)

Please try the branch out and let me know if things work. Naturally all this is implemented on top of v0.8 so if you haven't migrated to 0.8 yet, you need to do that first. There are some breaking changes between 0.7 and 0.8.

[koskimas]

Has anyone tested the branch? I'll leave thefrom('Foo as bar') syntax since it's supported by knex. I could also add an alias method for setting just the alias?

[jeff-kilbride]

@koskimas Sorry I didn't have a chance to give more feedback on this or test it much. I've been in the middle of some breaking changes in my database schema, so my test suite needs to be updated. I was just able to upgrade to 0.8.3 this evening.

The from method works great! 👍

Is the alias method working? I see it in the docs, but I'm getting an error when I use Table.query().alias('t'):

TypeError: Table.query(...).alias is not a function

Thanks!

--EDIT--

Sorry, my fault. The alias method is working, too! 🎉