CODEWITHSUNDEEP
mysqlnode.jsnode-mysqlknex.js
Javohir Mirzo Fazliddinov
Javohir Mirzo Fazliddinov

Reputation: 321

knex.js: combination of orWhere followed by multiple where

I am writing a query with knex.js and i got stuck with orWhere.

I need a query like this:

select 
    count(*) 
from 
    `Project` 
where 
    `Project`.`createdAt` >= '2019-11-12' 
and 
    `Project`.`createdAt` <= '2020-11-19' 
and 
    ((`Project`.`productType` = 1) 
or 
    (`Project`.`productType` = 2))

but for some reason this is what i am getting:

select 
    count(*) 
from 
    `Project` 
where 
    `Project`.`createdAt` >= '2019-11-12' 
and 
    `Project`.`createdAt` <= '2020-11-19' 
or 
    (`Project`.`productType` = 1) 
or 
    (`Project`.`productType` = 2)

Notice there are two 'or's which i want and in the place of first or

this is the code:

 builder.count('*')
.from('Project')
.where('Project.createdAt', '>=', '2019-11-12')
.where('Project.createdAt', '<=', '2019-11-19')
.orWhere({'Project.productType': 1})
.orWhere({'Project.productType': 2})

would appreciate any help

Upvotes: 2

Views: 6557

Answers (1)

ThangLe
ThangLe

Reputation: 970

I see, you can try that:

//
 builder.count('*')
.from('Project')
.where('Project.createdAt', '>=', '2019-11-12')
.where('Project.createdAt', '<=', '2019-11-19')
.where(function () {
    this.orWhere({'Project.productType': 1}).orWhere({'Project.productType': 2})
});

// or use arrow function
 builder.count('*')
.from('Project')
.where('Project.createdAt', '>=', '2019-11-12')
.where('Project.createdAt', '<=', '2019-11-19')
.where((bd) => {
    bd.orWhere({'Project.productType': 1}).orWhere({'Project.productType': 2})
});

Hope it works for you.

Upvotes: 13

Related Questions