How do I add an aliased constant to a Select?

[ajoslin103]

I really want to Select a synthetic column

    const contactRows = await AccessFamily.query()
      .select('FAMUID', '1 as persNum')
      .where('emailaddress', email)
      .fetch()

The knex docs say this will work, but I can't succeed !!

    const contactRows = await AccessFamily.query()
      .select('FAMUID', knex.ref('1').as('persNum'))
      .where('emailaddress', email)
      .fetch()

ER_BAD_FIELD_ERROR: Unknown column '1' in 'field list'

[thetutlage]

Do you have a column name 1 in your database table?

[ajoslin103]

no

the need is to create a column in the output named 'persNum' with the contents of '1'

in normal SQL it would be done like so: select '1' as persNum from someTable;

in Query builder this would alias the col 'person' as 'persNum'

  .select('FAMUID', 'person as persNum')

what I need to do is alias a constant, to create a column and a value, neither of which exist in the table

  .select('FAMUID', ' "1" as persNum ')

  .select('FAMUID', " '1' as persNum ")

these variations also result in the ER_BAD_FIELD_ERROR: Unknown column '1' in 'field list'

thanks for your time and attention

[thetutlage]

I suggest turning on the database query debugging and see the query being executed

[ajoslin103]

attempted:

      const trainingRow = await AccessTraining
        .query()
        .select('1 as one')
        .first();

logged:

{ method: 'first',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 1 ],
  __knexQueryUid: 'dffb99d0-2df9-4893-9cfd-5d9e0f12b5c4',
  sql: 'select `1` as `one` from `acc_training` limit ?' 
 }

error: PDFCertificates buildPdfForCertification() threw: select `1` as `persNum` from `acc_training` limit 1 - ER_BAD_FIELD_ERROR: Unknown column '1' in 'field list'

attempted:

      const knex = require('knex')({ debug: true, client: 'mysql' })

      const trainingRow = await AccessTraining
        .query()
        .select(knex.ref('1').as('persNum'))
        .first();

logged:

{ method: 'first',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 1 ],
  __knexQueryUid: '310a018b-4331-4315-9e46-fa9042c93b6b',
  sql: 'select `1` as `persNum` from `acc_training` limit ?' 
 }

error: PDFCertificates buildPdfForCertification() threw: select `1` as `persNum` from `acc_training` limit 1 - ER_BAD_FIELD_ERROR: Unknown column '1' in 'field list'

attempted:

      const trainingRow = await AccessTraining
        .query()
        .select("'1' as one")
        .first();

logged:

{ method: 'first',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 1 ],
  __knexQueryUid: '8e15d367-3f30-418d-9e24-fd7c2f92f00e',
  sql: 'select `\'1\'` as `one` from `acc_training` limit ?' 
 }

error: PDFCertificates buildPdfForCertification() threw: select `'1'` as `one` from `acc_training` limit 1 - ER_BAD_FIELD_ERROR: Unknown column ''1'' in 'field list'

[vikrambiwal]

Use this:

  const trainingRow = await AccessTraining
        .query()
        .select(Database.raw('"1" as one'))
        .first();