Using andOn inside innerJoinTableOnFunction not translated column according to definition

[ucin]

Issue type:

[X ] Question [ X] Bug report [ ] Feature request [ ] Documentation issue

Database system/driver:

[X ] Postgres [ ] MSSQL [ ] MySQL [ ] MariaDB [ ] SQLite3 [ ] Oracle [ ] Amazon Redshift

typed-knex version:

[ X] latest [ ] @next [ ] 0.x.x (or put your version here)

Knex.js version: 0.95.12

Query:

   .innerJoinTableOnFunction("sector", RecordTable, (join) => {
        join.andOnVal("group", "=", "MASTER_DATA")
            .andOnVal("key", "=", "SECTOR")
            .andOn("subKey", "=", "budget.sector");
    })

Table Definition:

@Table("records")
export class RecordTable {
    @Column({ primary: true, name: "id" })
    public id!: string;

    @Column({ name: "group" })
    public group!: string;

    @Column({ name: "key" })
    public key!: string;

    @Column({ name: "sub_key" })
    public subKey!: string;

    @Column({ name: "data" })
    public data!: string;
}

The subKey column is suppose to translate into "sector"."sub_key"

Now is generating something like this

INNER JOIN "records" AS "sector" ON "sector"."group" = $1
        AND "sector"."key" = $2
        AND "budget"."sector" = "sector"."subKey"

Postgres error hint:

 hint: 'Perhaps you meant to reference the column "sector.sub_key".',

[wwwouter]

Thanks for reporting this!

Can you check v4.5.0 to see if this resolves your problem?

[ucin]

Thank you for your support @wwwouter. I still see the column is not translated according to definition

Postgres error message

 column recordSector.subKey does not exist

Here is my query

 .innerJoinTableOnFunction("recordSector", RecordTable, (join) => {
          join.andOnVal("group", "=", "MASTER_DATA")
            .andOnVal("key", "=", "SECTOR")
            .andOn("subKey", "=", "sector");
    })

And the table definition is still the same.

Make sure is already updated.

[wwwouter]

Hmmm, that's too bad. If I add your RecordTable and change the "should inner join with function with other table using correct column name" test to this, it is successful:

it.only("should inner join with function with other table using correct column name", (done) => {
        const typedKnex = new TypedKnex(knex({ client: "postgresql" }));
        const query = typedKnex.query(UserSetting).innerJoinTableOnFunction("sector", RecordTable, (join) => {
            join.andOnVal("group", "=", "MASTER_DATA").andOnVal("key", "=", "SECTOR").andOn("subKey", "=", "id");
        });

        const queryString = query.toQuery();
        assert.equal(queryString, `select * from "userSettings" inner join "records" as "sector" on "sector"."group" = 'MASTER_DATA' and "sector"."key" = 'SECTOR' and "userSettings"."id" = "sector"."sub_key"`);

        done();
    });

Maybe there is another part of the query that is failing.

Could you maybe fork this repo, update "should inner join with function with other table using correct column name" with your entire query to see what is happening?

To see the test result, run npm run mocha:unit

[ucin]

Hi @wwwouter,

I follow your instruction and my code work without any modification! It turn out my local typed-knex is not being updated even the version is already the latest. I'm not sure if the issue is with lerna or npm.

Thanks again for your assistance! I really enjoy working with typed-knex!

[wwwouter]

Something went wrong when publishing. v4.5.1 should work