Knex.js divide value of a column by another column

Question

Hellow I'm searching how to create query like this with knex

SELECT product.price/100 AS priceInDollars

and getting error 'price/100 not found'

related question divide the value of a column by another column

Answer 1

Knex seems to wrap the columns in quotes, so such operations cannot be supported using Knex query builder, as the database would interpret that as literals.

knex.column('title', 'author', 'year').select().from('books')
Outputs:
select `title`, `author`, `year` from `books`

However, knex also provides a way to fire raw SQL statements, so you would be able to execute this query.

knex.raw('SELECT product.price/100 AS priceInDollars').then(function(resp) { ... });

Further reading: Knex Raw Queries

Answer 2

This can be done using knex.raw query partially for the columns.

You have two possible solutions:

1. Raw SQL: You have possibility to use knex.raw to use full raw SQL query as you would execute it against database (as other answers already indicated). However, if you are using tools like knex, usually this is something you want to avoid (especially when you are using query builder to build more complicated queries and relationships - I assume that this is why you are using knex in the first place).

2. You can use knex.raw partially for specific column instead.

Lets consider following query:

SELECT id, product.price/100 AS priceInDollars, created_at WHERE id='someUUID';

You can execute this with knex in a following format:

knex
    .select([
        'id',
        knex.raw('products.price::numeric/100 as priceInDollars'),
        'created_at'
    ])
    .from('products')
    .where({ id: 'someUUID' });

My assumption in the answer is that postgresql is used (hence numeric), but if you want to extract float after the division, you will need to do a casting (in dependency of what kind of types database support)