CODEWITHSUNDEEP
sqlnode.jspostgresql
Jitender
Jitender

Reputation: 7971

How to make SQL query when you have multiple if conditions

I am trying to build an API that supports, list, search, and filters. I am not sure where to put the where clause.

It works in some cases but doesn't in many. For example, when input body has search filed it will work but when you apply only the second filter which is pan in this case it didn't. 

const query = {
    text: `
      select master.contact.id,
      master.contact.name
      from
      master.contact
       `
  };
  if (input.search) {
    query.text += ` where
    (master.contact.pan ILIKE '${input.search}%' or master.contact.ira ILIKE '${input.search}%')`;
  }

  if (input.filters) {
    const {
      isActive,
      pan
    } = input.filters;
    if (isActive !== undefined) {
      query.text += ` where master.contact.isActive = ${isActive}`;
    }

    if (pan) {
      query.text += `and master.contact.pan = ${pan}`;
    }

Upvotes: 0

Views: 223

Answers (1)

dassoun
dassoun

Reputation: 79

There are different ways to do this. One of them would be to have

Where 1=1

In you main query. So you can add And condition for every condition you have.

const query = {
text: `
  select master.contact.id,
  master.contact.name
  from
  master.contact
  where 1=1
  `
}; 

If (input.search) { 
  query.text += ` and (master.contact.pan ILIKE '${input.search}%' or master.contact.ira ILIKE '${input.search}%')`;
}

And so on...

Upvotes: 1

Related Questions