Left join throws error

[Meldiron]

Issue type:

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

Database system/driver:

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

typed-knex version:

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

Knex.js version: "^0.16.5",

Steps to reproduce or a small repository showing the problem:

Code:

    const staticPagesP = SQLManager.typedKnex
      .query(TableStaticPages)
      .leftOuterJoinTableOnFunction('lang', TableStaticPagesLang, (join) => {
        join.on('pageIdPlain', '=', 'lang.id');
      })
      .select('lang.slug', 'lang.text', 'lang.title')
      .where('lang.langIdPlain', lang)
      .getMany();

Error:

  error Error: Unknown column 'lang.pageIdPlain' in 'on clause'
  error     at Packet.asError (/Users/meldiron/Desktop/ren-sk/Server/node_modules/mysql2/lib/packets/packet.js:684:17)
  error     at Query.execute (/Users/meldiron/Desktop/ren-sk/Server/node_modules/mysql2/lib/commands/command.js:28:26)
  error     at Connection.handlePacket (/Users/meldiron/Desktop/ren-sk/Server/node_modules/mysql2/lib/connection.js:449:32)
  error     at PacketParser.onPacket (/Users/meldiron/Desktop/ren-sk/Server/node_modules/mysql2/lib/connection.js:72:12)
  error     at PacketParser.executeStart (/Users/meldiron/Desktop/ren-sk/Server/node_modules/mysql2/lib/packet_parser.js:75:16)
  error     at Socket.<anonymous> (/Users/meldiron/Desktop/ren-sk/Server/node_modules/mysql2/lib/connection.js:79:25)
  error     at Socket.emit (events.js:315:20)
  error     at Socket.EventEmitter.emit (domain.js:483:12)
  error     at addChunk (_stream_readable.js:295:12)
  error     at readableAddChunk (_stream_readable.js:271:9)
  error     at Socket.Readable.push (_stream_readable.js:212:10)
  error     at TCP.onStreamRead (internal/stream_base_commons.js:186:23) +0ms

Query:

  knex:query select `lang`.`slug` as `lang.slug`, `lang`.`text` as `lang.text`, `lang`.`title` as `lang.title` from `static_pages` left outer join `static_pages_lang` as `lang` on `lang`.`id` = `lang`.`pageIdPlain` where `lang`.`lang_id` = ? undefined +24ms

From what I can see, it did not translate javascript variable name to mysql column name correctly (from pageIdPlain to page_id)

Schema:

@Table('static_pages')
export class TableStaticPages {
  @Column({ name: 'id', primary: true })
  id: number;
  @Column({ name: 'internal_name' })
  internalName: string;
}

@Table('static_pages_lang')
export class TableStaticPagesLang {
  @Column({ name: 'id', primary: true })
  id: number;
  @Column({ name: 'page_id' })
  pageId: TableStaticPages | null;
  @Column({ name: 'page_id' })
  pageIdPlain: number;
  @Column({ name: 'lang_id' })
  langId: TableLangs | null;
  @Column({ name: 'lang_id' })
  langIdPlain: string;
  @Column({ name: 'slug' })
  slug: string;
  @Column({ name: 'title' })
  title: string;
  @Column({ name: 'text' })
  text: string;
}

Note: This might be miss-use of some function or wrong schema setup by me, I recently migrated from really old version of typed knex to latest. Please let me know.

[wwwouter]

Hi Meldiron,

Good to see you're still using the library!

I just added a new function to make joins like these a bit simpler: https://github.com/wwwouter/typed-knex#leftouterjoin

In this case, I think this is what you want:

 const staticPagesP = SQLManager.typedKnex
      .query(TableStaticPages)
      .leftOuterJoin('lang', TableStaticPagesLang, 'pageIdPlain', '=', 'lang.id')
      .select('lang.slug', 'lang.text', 'lang.title')
      .where('lang.langIdPlain', lang)
      .getMany();

[Meldiron]

Hello, sure thing, I love your library! Please, let me know if there is anything I can help you with.

Anyway, the update seems to work, but new problem came up. I had to use id instead of lang.id in the example above, is that fine?

Anyway, after updating to this new method, I still get an error. If I am not mistaken, once again, this seems to be renaming issue but this time in WHERE function.

Code:

    const productsP = SQLManager.typedKnex
      .query(TableProducts)
      .leftOuterJoin('lang', TableProductsLang, 'productIdPlain', '=', 'id')
      .select(
        'id',
        'isDeprecated',
        'isSpecialEdition',
        'backgroundColor',
        'price',
        'stock',
        'imageIdPlain',
        'lang.slug',
        'lang.title',
        'lang.producer',
        'lang.ingredients',
        'lang.description',
        'lang.ingredients',
        'lang.warning'
      )
      .where('lang.langIdPlain', lang)
      .getMany();

Error:

  error Error: Unknown column 'lang.langIdPlain' in 'where clause'
  error     at Packet.asError (/Users/meldiron/Desktop/ren-sk/Server/node_modules/mysql2/lib/packets/packet.js:684:17)
  error     at Query.execute (/Users/meldiron/Desktop/ren-sk/Server/node_modules/mysql2/lib/commands/command.js:28:26)
  error     at Connection.handlePacket (/Users/meldiron/Desktop/ren-sk/Server/node_modules/mysql2/lib/connection.js:449:32)
  error     at PacketParser.onPacket (/Users/meldiron/Desktop/ren-sk/Server/node_modules/mysql2/lib/connection.js:72:12)
  error     at PacketParser.executeStart (/Users/meldiron/Desktop/ren-sk/Server/node_modules/mysql2/lib/packet_parser.js:75:16)
  error     at Socket.<anonymous> (/Users/meldiron/Desktop/ren-sk/Server/node_modules/mysql2/lib/connection.js:79:25)
  error     at Socket.emit (events.js:315:20)
  error     at Socket.EventEmitter.emit (domain.js:483:12)
  error     at addChunk (_stream_readable.js:295:12)
  error     at readableAddChunk (_stream_readable.js:271:9)
  error     at Socket.Readable.push (_stream_readable.js:212:10)
  error     at TCP.onStreamRead (internal/stream_base_commons.js:186:23) +0ms

Query:

  knex:query select `products`.`id` as `id`, `products`.`is_deprecated` as `isDeprecated`, `products`.`is_special_edition` as `isSpecialEdition`, `products`.`background_color` as `backgroundColor`, `products`.`price` as `price`, `products`.`stock` as `stock`, `products`.`image_id` as `imageIdPlain`, `lang`.`slug` as `lang.slug`, `lang`.`title` as `lang.title`, `lang`.`producer` as `lang.producer`, `lang`.`ingredients` as `lang.ingredients`, `lang`.`description` as `lang.description`, `lang`.`ingredients` as `lang.ingredients`, `lang`.`warning` as `lang.warning` from `products` left outer join `products_lang` as `lang` on `lang`.`product_id` = `products`.`id` where `lang`.`langIdPlain` = ? undefined +1ms

Problem: wherelang.langIdPlain= ?

It does not rename correctly. My schema (simplified) for this query is:

@Table('products_lang')
export class TableProductsLang {
  @Column({ name: 'id', primary: true })
  id: number;
  @Column({ name: 'product_id' })
  productId: TableProducts | null;
  @Column({ name: 'product_id' })
  productIdPlain: number;
  @Column({ name: 'lang_id' })
  langId: TableLangs | null;
  @Column({ name: 'lang_id' })
  langIdPlain: string;

  @Column({ name: 'description' })
  description: string;
  // and others ...
}

@Table('products')
export class TableProducts {
  @Column({ name: 'id', primary: true })
  id: number;
  @Column({ name: 'internal_name' })
  internalName: string;
  // and others ...
}

[wwwouter]

Ah, you're right, there was a problem with the where code, where the alias of a joined column was not used.

Column aliasing doesn't seemed to be used a lot, so there can be some more of these bugs..

I created a new version: v.4.2.1. Can you test to see if this fixes your problem?

[Meldiron]

Thanks, I can confirm that now it works perfectly in over 10 scenarios in my code.