Sequelize conditional inclusion of where clause nodejs

Question

I have this code, which has multiple where clause:

Time_Sheet_Details.findAll({ include: [ { model: timesheetNotesSubcon, required: false, attributes:["note","file_name", "id", "working_hrs", "timestamp", "has_screenshot", "notes_category"] }, { model: Timesheet, attributes:["id","leads_id","userid"], where: {leads_id: filters.leads_id}, // Client include:[ { model: Lead_Info, attributes:["id","fname","lname","email","hiring_coordinator_id","status"], where: {hiring_coordinator_id: {$in: filters.sc_id}}, // SC include:[{ model: adminInfoSchema, required: false, attributes:["admin_id","admin_fname", "admin_lname", "admin_email", "signature_contact_nos", "signature_company"], }] }, { model:Personal_Info,attributes:["userid","fname","lname","email"], where: {userid: filters.subcon_id}, // Subcon } ] }], where: { reference_date: filters.reference_date }, order:[ ["id","DESC"] ], offset:((page-1)*limit), limit : limit, subQuery:false }).then(function(foundObject){ willFulfillDeferred.resolve(foundObject); });

The where clause is the one with the comment Client, SC and Subcon. However, what is the best approach if those where clause is optional? I am using that for search filter. So if filters.leads_id is null then the where: {leads_id: filters.leads_id}, // Client should not be included in the query. Same with the others. The only solution I can think of is repeat those code blocks for each scenario of not null parameters but that's to repetitive and not practical.

Any other approach or solutions?

joshua.paling · Accepted Answer · 2017-05-02 11:13:26Z

If I understand correctly, I think as a first step, you should define your respective where clauses, conditionally upon wether or not each specific search criteria is set:

const clientWhere = filters.leads_id ? {leads_id: filters.leads_id} : {} const scWhere = filters.sc_id ? {hiring_coordinator_id: {$in: filters.sc_id}} : {} const subconWhere = filters.subcon_id ? {userid: filters.subcon_id} : {}

So at this point if a search option isn't set, there'll just be an empty object as the where clause.

Next, use those pre-defined where clause objects in your query:

Time_Sheet_Details.findAll({ include: [ { model: timesheetNotesSubcon, required: false, attributes:["note","file_name", "id", "working_hrs", "timestamp", "has_screenshot", "notes_category"] }, { model: Timesheet, attributes:["id","leads_id","userid"], where: clientWhere, // Client include:[ { model: Lead_Info, attributes:["id","fname","lname","email","hiring_coordinator_id","status"], where: scWhere, // SC include:[{ model: adminInfoSchema, required: false, attributes:["admin_id","admin_fname", "admin_lname", "admin_email", "signature_contact_nos", "signature_company"], }] }, { model:Personal_Info,attributes:["userid","fname","lname","email"], where: subconWhere, // Subcon } ] }], where: { reference_date: filters.reference_date }, order:[ ["id","DESC"] ], offset:((page-1)*limit), limit : limit, subQuery:false }).then(function(foundObject){ willFulfillDeferred.resolve(foundObject); });

Thanks for your answer. For some reason my code did not work with an empty object {}, I had to use undefined such as const clientWhere = filters.leads_id ? {leads_id: filters.leads_id} : undefined

Collectives™ on Stack Overflow

Sequelize conditional inclusion of where clause nodejs

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related