How to create a select using adonisjs

I need create this select:

SELECT DISTINCT(data) as sigla, to_char(“data”, ‘DD/MM/YYYY’) as nome FROM imagem WHERE modelo = ‘${modelo}’ AND regiao = ‘${regiao}’ AND parametro = ‘${parametro}’ ORDER BY data DESC;

i created this:
const imagem = await Imagem.query()
.distinct(‘data as sigla’)
.where(‘modelo’, modelo)
.where(‘regiao’, regiao)
.where(‘parametro’, parametro)
.orderBy(‘data’, ‘DESC’)
.fetch();

how to implement:
to_char(“data”, ‘DD/MM/YYYY’) in this select ??

hi, welcome @Sylvio

can try this https://adonisjs.com/docs/4.1/query-builder#_raw_queries

await Database.raw('SELECT DISTINCT(data) as sigla,......', [modelo])

regards!

@codiplace
does not work :confused:

My code where:
async index ({ request, response }) {
const modelo = request.params.modelo;
try {
const imagem = await Imagem.raw('SELECT DISTINCT(data) as sigla from imagem where modelo = ? ', [modelo]);
return response.json(imagem);
} catch (error) {
return response.status(400).json({ error: ‘No registered modelo’ });
}
}

you need import Database.

const Database = use("Database");

some like that

await Database.raw( "SELECT DISTINCT(data) as sigla, to_char('data', ‘DD/MM/YYYY’) as nome FROM imagem WHERE modelo = ? AND regiao = ? AND parametro = ? ORDER BY data DESC", [modelo, regiao, parametro]);
1 Like

Thx @codiplace :+1:

 const imagem = await Database.raw("SELECT DISTINCT(data) as sigla, to_char(\"data\", 'DD/MM/YYYY') as nome FROM imagem WHERE modelo = ? AND regiao = ? AND parametro = ? ORDER BY data DESC", [modelo, regiao, parametro]);
      return response.json(imagem.rows);
2 Likes