Empty sql template or condtional template implementation

[lineldcosta]

Hello all,

I have the following query, if region is there then append region to the main query & country exists then append the same to the main query and similarly for city and area.

But i am not able to execute the query if any one of those are empty. How can i implement this. guys any input here plz?

 public async getProductLaunch(region, country, city, area){
    try {
      let queryRegion, queryCountry, queryCity, queryArea;
      if (region) {
        queryRegion = sql` AND p.region_id IN (${region})`;
      } else {
        queryRegion = sql` `;
      }
      if (country) {
        queryCountry = sql` AND p.country_id IN (${country})`;
      } else {
        queryCountry = sql` `;
      }
      if (city) {
        queryCity = sql` AND p.city_id IN (${city})`;
      } else {
        queryCity = sql` `;
      }
      if (area) {
        queryArea = sql` AND ${sql.array(area.split(','), 'int4')} && p.area_id`;
      } else {
        queryArea = sql` `;
      }
      let result: any = await this.db.query(sql`
                          SELECT t.id tagid, t.name tag, p.id productid, p.name productname, pr.path image
                          FROM tag t
                          LEFT JOIN product_tag pt ON t.id = pt.tag_id
                          LEFT JOIN product p ON pt.product_id = p.id
                          LEFT JOIN product_resource pr ON p.id = pr.product_id AND pr.type = 'IMAGE' AND pr.default = '1'
                          WHERE t.status = 'ACTIVE'
                          AND p.status = 'ACTIVE'
                          ${queryRegion}
                          ${queryCountry}
                          ${queryCity}
                          ${queryArea}
                          ORDER BY t.id; `);

      if (result.rowCount > 0) {
        return result.rows;
      }
      return false;
    } catch (e) {
      throw e;
    }
  }

[gajus]

Use sql.booleanExpression.

[baerrach]

sql.booleanExpression was removed in favour of sql.join but the article has not been updated.

See feat: remove multiple methods in favor of sql.join