CODEWITHSUNDEEP
javascriptsqlsqlitequery-builderknex.js
B.Balamanigandan
B.Balamanigandan

Reputation: 4875

Knex.js Where Clause contains mandatory AND and Optional OR - SQL

I'm having a SQLite database table of Employees

Table Name: Employee

ID    Name    Country   CountyID   Status
1     John    IN        1          Active
2     Jack    US        1          InActive
3     Emma    UK        1          Active
4     Josh    US        1          Active
5     Jill    US        0          Active

I need to fetch 'Active' person whose are residing in 'IN' and 'US' OR CountyID '1'.

Sample Code:

const dbContext = require("knex")({
        client: "sqlite3",
        connection: {
            filename: filename.db
        },
        useNullAsDefault: true
});

const persons = dbContext('Employee').where({Status: 'Active'}). ???

SQL Query is 

SELECT * FROM Employee WHERE Status = 'Active' AND (Country IN ('IN', 'US') OR CountyID = 1)

I required the equivalent Knex Query. Kindly assist me in this regards.

Upvotes: 0

Views: 1542

Answers (1)

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

Somethin like this should do it:

dbContext('Employee').where('Status', 'Active').where(builder => {
  builder.whereIn('Country', ['IN', 'US']).orWhere('CountyID', 1);
})

Upvotes: 1

Related Questions