objection.js: joinEager doesn't work with table names that contain a postgresql schema

I have a query that looks similar to this:

Thing.query().first().select(
    "thingId", "creationTime", "author.emailAddress"
).joinRelation("author" /* Join on users table */ ).where({
    "thingId": thingId
}).then(thing => doStuff(thing))

Both the thing and user tables have a creationTime column. When I try to express the query like above I get an error column reference "creationTime" is ambiguous.

One solution would be to do this, but I really don’t want to because it’s verbose and repetitive and I’d have to do it in lots of places. It wouldn’t help maintainability at all.

Thing.query().first().select(
    "thingId", "thingSchemaName.thingTableName.creationTime", "author.emailAddress"
).joinRelation("author" /* Join on users table */ ).where({
    "thingId": thingId
}).then(thing => doStuff(thing))

What I would very much like to do and would expect to be able to do - except I haven’t worked out any way how - is to select the model table with an alias. Something nice and sensible and readable like

Thing.query().as("thing").first().select(
    "thing.thingId", "thing.creationTime", "author.emailAddress"
).joinRelation("author" /* Join on users table */ ).where({
    "thing.thingId": thingId
}).then(thing => doStuff(thing))

Or, it would be quite nice if objection was able to automagically prepend the models’ table name to selected columns that don’t otherwise specify.

What’s the best way to fix this?

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 31

Commits related to this issue

fix bug with joinEager and table name with postgres schema in it. Related to issue #439 — committed to Vincit/objection.js by koskimas 7 years ago
fix bug with joinEager and table name with postgres schema in it. Related to issue #439 — committed to Vincit/objection.js by koskimas 7 years ago

Most upvoted comments

Sorry I’m an idiot

I forgot to restart the server

pineapplemachine on Jul 12, 2017

By the way, a useful pattern to remove password hashes is to delete them in $formatJson like this:

class User extends Model {
  $formatJson(json) {
    json = super.$formatJson(json);
    // Never leak password to the outside world.
    delete json.password;
    return json;
  }
}

koskimas on Jul 12, 2017

You can select columns using modifyEager like this:

        const query = app.db.Questionnaire.alias("questionnaire").first().joinEager(
            "[author, title]"
        ).select(
            "questionnaire.questionnaireId", "questionnaire.creationTime"
        ).modifyEager('author', builder => {
            builder.select("emailAddress", "blaaBlaa");
        }).where({
            "questionnaire.questionnaireId": request.body.questionnaireId,
            "questionnaire.disabled": false,
        });

You need to use modifyEager or follow the weird naming convention joinEager uses to be able to parse the flat result rows into a tree. modifyEager works with normal eager as well as the join based joinEager.

koskimas on Jul 12, 2017

I messed up the original commit, force pushed a new one and edited the message. That’s why 😄

koskimas on Jul 12, 2017

You can use the alias method:

Thing.query().alias("thing").first().select(
    "thing.thingId", "thing.creationTime", "author.emailAddress"
).joinRelation("author" /* Join on users table */ ).where({
    "thing.thingId": thingId
}).then(thing => doStuff(thing))

koskimas on Jul 11, 2017