CODEWITHSUNDEEP
javascriptmysqlsqlnode.js
Shidomaru NeveRage
Shidomaru NeveRage

Reputation: 276

How to improve homemade SQL syntax from filter function?

I'm doing an API and the following code is used to retrieve users depending on filter. ( POST /users/search )

At the end of the function, I'm working with "LIMIT" and "OFFSET" in SQL because I want that nobody can retrieve more than 10 entries.

The code to generate the SQL query from WHERE is terrible. I am looking to improve it but I can't find a more effective way. Do you have any ideas?

 function getSqlFilter(filter) {
  var filterSql = '';
  if (!util.isEmpty(filter)) {
    var isFiltered = false;
    if (filter.userId != null) {
      if (!isFiltered) filterSql += ' WHERE ';
      if (isFiltered) filterSql += ' AND ';
      filterSql += 'pk_user in (' + filter.userId.toString() + ')';
      isFiltered = true;
    }
    if (filter.username != null) {
      if (!isFiltered) filterSql += ' WHERE ';
      if (isFiltered) filterSql += ' AND ';
      filterSql += 'username in (' + filter.username.toString() + ')';
      isFiltered = true;
    }
    if (filter.email != null) {
      if (!isFiltered) filterSql += ' WHERE ';
      if (isFiltered) filterSql += ' AND ';
      filterSql += 'email in (' + filter.email.toString() + ')';
      isFiltered = true;
    }
    if (filter.society != null) {
      if (!isFiltered) filterSql += ' WHERE ';
      if (isFiltered) filterSql += ' AND ';
      filterSql += 'society in (' + filter.society.toString() + ')';
      isFiltered = true;
    }
    if (filter.firstname != null) {
      if (!isFiltered) filterSql += ' WHERE ';
      if (isFiltered) filterSql += ' AND ';
      filterSql += 'firstname in (' + filter.firstname.toString() + ')';
      isFiltered = true;
    }

    if ((filter.start != null) && (filter.end != null)) {
      filter.start -= 1;
      var limit = filter.end - filter.start;
      if (limit <= 10) {
        filterSql += 'LIMIT ' + limit + ' OFFSET ' + filter.start;
      }
    } else if (filter.start != null) {
      filterSql += 'LIMIT 10 OFFSET ' + filter.start;
    } else {
      filterSql += 'LIMIT 10 OFFSET 0';
    }
  } else {
    filterSql += 'LIMIT 10 OFFSET 0';
  }
  return filterSql;
}

Thanks for helping

Upvotes: 0

Views: 89

Answers (3)

Rajender Joshi
Rajender Joshi

Reputation: 4205

You can simplify the redundant logic and utilize ES6 features for shorter syntax.

function getSqlFilter(filter = {}) {
    const searches = Object.keys(filter);
    const sLength = searches.length
    let filterSql = sLength === 0 ? "LIMIT 10 OFFSET 0" : "WHERE ";
    const map = {
        userId: "pk_user",
        username: "username",
        email: "email",
        society: "society"
    }
    const { start, end } = filter;
    const hasOnlyStart = start !== undefined && end === undefined;
    const hasStartAndEnd = start !== undefined && end !== undefined;

    if (sLength) {
        filterSql += searches.map(search => {
            if(search === "start") {
                return hasOnlyStart ? `LIMIT ${filter[search]} OFFSET 10` : "";
            }
            if(search === "end") {
                return hasStartAndEnd ? `LIMIT ${filter[search]} OFFSET ${end - start}` : "";
            }
            return `${map[search]} in (${filter[search].toString()})`;
        }).join(' AND ');
    }
    return filterSql;
}

Upvotes: 1

王仁宏
王仁宏

Reputation: 396

It is my anwser:

function getSqlFilter(filter) {
  let sql = '';
  let start = parseInt(filter.start);
  let end = parseInt(filter.end);
  delete filter.start;
  delete filter.end;
  const conditions = [];
  const validKey = [...your key];
  for (const key in filter){
    if(validKey.indexOf(key) !== -1){
      if(key === 'userId') conditions.push(`pk_user in (${filter[key].toString()})`);
      else conditions.push(`${key} in (${filter[key].toString()})`);
    }
  }
  if(conditions.length){
    sql = ` WHERE ${conditions.join(' AND ')}`;
  }
  let limit = 10;
  if (start) {
    if(start < 0) start = 0;
    if (end) {
      let be_limit = start - end + 1;
      if(be_limit <= 10 && be_limit >= 0) limit = be_limit;
    }
  } else {
    start = 0;
  }
  sql += ` LIMIT ${limit} OFFSET ${start}`;
  return sql;
}

If you don't want to change filter, please clone it before delete props.

Upvotes: 1

thopaw
thopaw

Reputation: 4044

I normally do this by starting with an always true condition

let filter= `...
   WHERE 1 = 1`

Then you can simply append the filter

if (filter.username != null) {
  filter += ' AND username in (' + filter.username.toString() + ')'
}
...

Hope this helps.

Upvotes: 1

Related Questions