I am trying to convert SQL query into KnexJS format, but current KnexJS query gives me the following error.
Here are the original query and the one I've been working on for the KnexJS. Please correct my KnexJS query. Basically, I would like to know how to build KnexJS query - inner join subquery Thank you in advance!
Original SQL query:
select DATE_RANGE.START_DATE, DATE_RANGE.END_DATE, count (distinct DATE) as DATE_COUNT
from TASK_HISTORY
join
(select
STORE_ID,
to_number(to_char(to_date(to_char(DATE,'99999999'),'YYYYMMDD') - 1,'YYYYMMDD'),'99999999') as END_DATE
, count (distinct DATE) as REC_COUNT
, to_number(to_char(to_date(to_char(lag (DATE) over (order by DATE asc),'99999999'),'YYYYMMDD') + 1,'YYYYMMDD'),'99999999') as START_DATE
, count (case when FINISH_TIME is not null then 1 end) as COUNT_FINISHED
, count (case when FINISH_TIME is null then 1 end) as COUNT_UNFINISHED
from TASK_HISTORY
where STORE_ID = 43
group by DATE, STORE_ID
having count (case when FINISH_TIME is not null then 1 end) = 0
order by DATE)
as DATE_RANGE
on TASK_HISTORY.DATE >= DATE_RANGE.START_DATE
AND TASK_HISTORY.DATE <= DATE_RANGE.END_DATE
AND TASK_HISTORY.STORE_ID = 43
group by DATE_RANGE.START_DATE, DATE_RANGE.END_DATE, DATE_RANGE.REC_COUNT
order by DATE_COUNT desc, START_DATE desc
Update:
Here is the sulution that worked for me:
await db
.table("task_history")
.select('date_range.start_date', 'date_range.end_date')
.select(db.raw(`count(distinct date) as date_count`))
.join(
db
.select('task_history.store_id')
.table('task_history')
.select(db.raw(
`to_number(to_char(to_date(to_char(date,'99999999'),'YYYYMMDD') - 1,'YYYYMMDD'),'99999999') as end_date`
))
.select(db.raw(`count(distinct date) as rec_count`))
.select(db.raw(
`to_number(to_char(to_date(to_char(lag (date) over (order by date asc),'99999999'),'YYYYMMDD') + 1,'YYYYMMDD'),'99999999') as start_date`
))
.select(db.raw(`count(case when FINISH_TIME is not null then 1 end) as COUNT_FINISHED`))
.select(db.raw(`count(case when FINISH_TIME is null then 1 end) as COUNT_UNFINISHED`))
.where('task_history.store_id', 43)
.groupBy('task_history.date', 'task_history.store_id')
.having(db.raw(`count(case when FINISH_TIME is not null then 1 end) = 0 order by date`))
.as('date_range'),
function () {
this.on('task_history.date', '>=', 'date_range.start_date')
.andOn('task_history.date', '<=', 'date_range.end_date')
.andOn('task_history.store_id', 43)
}
)
.groupBy('date_range.start_date', 'date_range.end_date', 'date_range.rec_count')
.orderBy('date_count', 'desc')
.orderBy('start_date', 'desc')
This might help you-
const sql = db.table("task_history")
.select('DATE_RANGE.START_DATE', 'DATE_RANGE.END_DATE')
.select(db.raw(`count(distinct DATE) as DATE_COUNT`))
.innerJoin(
db.select('store_id')
.table('task_history')
.select(db.raw(
`to_number(to_char(to_date(to_char(DATE,'99999999'),'YYYYMMDD') - 1,'YYYYMMDD'),'99999999') as END_DATE`
))
.select(db.raw(`count(distinct DATE) as REC_COUNT`))
.select(db.raw(
`to_number(to_char(to_date(to_char(lag (DATE) over (order by DATE asc),'99999999'),'YYYYMMDD') + 1,'YYYYMMDD'),'99999999') as START_DATE`
))
.select(db.raw(`count(case when FINISH_TIME is not null then 1 end) as COUNT_FINISHED`))
.select(db.raw(`count(case when FINISH_TIME is null then 1 end) as COUNT_UNFINISHED`))
.where('store_id', 43)
.groupBy('date', 'store_id')
.having(db.raw(`count(case when FINISH_TIME is not null then 1 end) = 0 order by DATE`))
.as('DATE_RANGE')
, function () {
this.on('DATE_RANGE.START_DATE', '>=', 'TASK_HISTORY.DATE')
.andOn('TASK_HISTORY.DATE', '<=', 'DATE_RANGE.END_DATE')
.andOn('TASK_HISTORY.STORE_ID', 43)
})
.where('task_history.date', '>=', 'DATE_RANGE.START_DATE')
.where('task_history.date', '<=', 'DATE_RANGE.END_DATE')
.groupBy('DATE_RANGE.START_DATE', 'DATE_RANGE.END_DATE', 'DATE_RANGE.REC_COUNT')
.orderBy('DATE_COUNT', 'desc')
.orderBy('START_DATE', 'desc')
.toSQL();
console.log(sql);