I am having a trouble translating DISTINCT ON clause to knex.js and there might also couple of errors along the way with multiple andOn inside join(). Could not find ways to translate it properly.
select DISTINCT ON (f1.operdate) f1.operdate as date,
f2.operdate as opendate,
opercodes.naenter code hereme as name,
f1.person as person,
f1.comments as comments,
f2.person as conductor
from cashboxdiary as f1
inner join opercodes on (opercodes.id = f1.opercode)
inner join cashboxdiary f2 on (f2.company = f1.company and f2.cashbox = f1.cashbox and f2.opercode = 3 and f2.operdate < f1.operdate)
What I have tried so far
knex("cashboxdiary")
.innerJoin("opercodes", "opercodes.id", "cashboxdiary.opercode")
.join('cashboxdiary as f2',function () {
this.on('f2.company','=','cashboxdiary.company')
.andOn('f2.cashbox','=','cashboxdiary.cashbox')
.andOn('f2.opercode','=',knex.raw('?',[3]))
.andOn('f2.operdate','<','cashboxdiary.operdate');
})
.where({ "cashboxdiary.company": company, "cashboxdiary.cashbox": cashbox})
.whereNotIn("cashboxdiary.opercode", [3, 4, 5, 6])
.andWhereBetween(knex.raw("cashboxdiary.operdate::date"), [
dateFrom.format(),
dateTo.format()
])
.select(knex.raw('distinct on (cashboxdiary.operdate) cashboxdiary.operdate as date,f2.operdate as opendate, opercode.name as name, cashboxdiary.person as person, cashboxdiary.comments as comments, f2.person as conductor'))