CODEWITHSUNDEEP
sqlknex.js
notentered
notentered

Reputation: 145

Converting SQL query into KnexJS

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.

Thank you in advance!

Original SQL query:

select count(distinct date) 
from task_history
where 
store_id = 100 and date >
(select date from (
select date, count(*) as count_all, count(case when finish_time is not null 
then 1 else null end) as count_finished
from task_history
where store_id = 100 
group by date
order by count_finished, date desc
fetch first row only) as get_max_date)

KnexJS query:

.table("task_history")
.count(db.raw("'distinct date'"))
.where('store_id', 100)
.where('date', '>', function() {
    this.select('date')
    .from(function() {
        this.select('date')
        .table("task_history")
        .first()
        .count('* as count_all')
        .count(db.raw(`case when finish_time is not null then 1 else null end as count_finished`))
        .where('store_id', 100)
        .groupBy('date')
        .orderBy('count_finished', 'desc')
        .orderBy('date', 'desc')
        .as('get_max_date')
    })
})

Upvotes: 0

Views: 1542

Answers (2)

notentered
notentered

Reputation: 145

The following query worked for me: 

.table("task_history")
  .count(db.raw("distinct date"))
  .where('store_id', 100)
  .where('date', '>', function() {
    this.select('date')
      .from(function() {
        this.select('date')
          .table("task_history")
          .first()
          .count('* as count_all')
          .select( db.raw("count(case when finish_time is not null then 1 else null end) as count_finished"))
          .where('store_id', 100)
          .groupBy('date')
          .orderBy('count_finished', 'asc')
          .orderBy('date', 'desc')
          .as('get_max_date')
      })
  });

Upvotes: 0

GaryL
GaryL

Reputation: 1460

That is a complex query. Since you haven't shared the SQL structure for others to attempt the same query, I would suggest that you try including this 'debugging' clause in your query:

.on('query-error', function(ex, obj) {
    console.log("KNEX query-error ex:", ex, "obj:", obj);
})

This will output the generated SQL for you when the query crashes. That may show you which part of the statement is incorrect.

Good luck.

Upvotes: 1

Related Questions