make postgres condition query in nodejs

Question

I have select query and I want to add filter and search values to select query. The user can perform a search and filter separately and together as well. Following are the scenario:

when I applied only search (Success)

select name, id from master.contact where name ilike 'sal%' or name ilike 'man%';

when I applied only first filter (Success)

select name, id from master.contact where isActive=true;

when I applied both filters (Success)

select name, id from master.contact where isActive=true and pan='abcd';

when I applied only the second filter (Fail)

select name, id from master.contact and pan='abcd';

when I applied search and both filters (Fail)

select * from master.contact where name ilike 'sal%' or name ilike 'man%' where master.contact.is_active = true and pan='abcd';

when I applied search and first filter (Fail)

select * from master.contact where name ilike 'sal%' or name ilike 'man%' where master.contact.is_active = true;

nodejs

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}`;
    }

Nick · Accepted Answer · 2019-04-20 07:27:37Z

2

How about collect the criteria in an array then join and append it, like so:

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

// Create an array to store any criteria
var where = [];

  if (input.search) {
    where.push(`(master.contact.pan ILIKE '${input.search}%' or master.contact.ira ILIKE '${input.search}%')`);
  }

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

    if (pan) {
      where.push(`master.contact.pan = ${pan}`);
    }

// If criteria were added, append to query text
if (where.length > 0) {
   query.text += ' where ' + where.join(' and ');
}

answered Apr 20, 2019 at 7:27

Nick

7,4912 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jitender Over a year ago

I end up using where 1=1. but this is nice

Collectives™ on Stack Overflow

make postgres condition query in nodejs

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related