Search code examples
knex.jsbookshelf.js

How do I get SQL LEFT with alias into bookshelf?

I need this SQL (works perfectly in mysql console) :

SELECT LEFT(authors.last_name,1) AS first_char

In "bookshelf.js".

While I get this far:

let result = await qb.column('last_name').select().from('authors').as('first_char');

I can't get 

let result = await qb.column('last_name').select('LEFT(authors.last_name,1)').from('authors').as('first_char');

this going.

It results in the following error:

Error: ER_BAD_FIELD_ERROR: Unknown column 'LEFT(authors.last_name,1)' in 'field list'

which does not make sense to me, because the column authors.last_name is still there.

Solution

  • .select() protects against SQL injection so you can't just pass random SQL to it and expect it to work. You need to use raw expressions for that:

    qb.column('last_name').select(qb.raw('LEFT(authors.last_name,1)')).from('authors').as('first_char');

    And on that note, technically this isn't a bookshelf question since the query builder is just knex.