[Question] Build SQL queries using HTTP query parameter

[jimzhan]

Exactly is the product we've been looking for, thanks for bringing this fantastic library to Node.js (PG). As the title, now we are using Objection-find, with further development, ORM is becoming harder to read and maintain. I was wondering if there's any good alternative you can suggest if we move to Slonik? :-)

[gajus]

Here is an example of how I do it:

// @flow

import {
  sql
} from 'slonik';
import {
  UserError
} from '../errors';

export default (eventFilter) => {
  const expressions = [
    sql.raw('TRUE = TRUE')
  ];

  if (eventFilter.countryIds.length) {
    expressions.push(
      sql.raw('ec1.country_id = ANY($1)', [
        sql.array(eventFilter.countryIds, 'int4')
      ])
    );
  }

  if (eventFilter.movieIds.length) {
    expressions.push(
      sql.raw('ec1.movie_id = ANY($1)', [
        sql.array(eventFilter.movieIds, 'int4')
      ])
    );
  }

  if (eventFilter.cinemaIds.length) {
    expressions.push(
      sql.raw('ec1.cinema_id = ANY($1)', [
        sql.array(eventFilter.cinemaIds, 'int4')
      ])
    );
  }

  if (!eventFilter.eventFromTime) {
    throw new UserError('Event filter must specify event time range. Missing "eventFromTime".');
  }

  if (!eventFilter.eventToTime) {
    throw new UserError('Event filter must specify event time range. Missing "eventToTime".');
  }

  const DAY_IN_MILLISECONDS = 86400 * 1000;
  const EVENT_TIME_RANGE_DAY_LIMIT = 60;

  if (eventFilter.eventFromTime && eventFilter.eventToTime && eventFilter.eventToTime - eventFilter.eventFromTime > EVENT_TIME_RANGE_DAY_LIMIT * DAY_IN_MILLISECONDS) {
    throw new UserError('Event filter time range must select no more than 60 days.');
  }

  if (eventFilter.eventFromTime) {
    expressions.push(
      sql.comparisonPredicate(
        sql.identifier([
          'ec1',
          'event_start_time'
        ]),
        '>=',
        sql.raw('to_timestamp($1)', [
          Math.round(eventFilter.eventFromTime / 1000)
        ])
      )
    );
  }

  if (eventFilter.eventToTime) {
    expressions.push(
      sql.comparisonPredicate(
        sql.identifier([
          'ec1',
          'event_start_time'
        ]),
        '<=',
        sql.raw('to_timestamp($1)', [
          Math.round(eventFilter.eventToTime / 1000)
        ])
      )
    );
  }

  if (eventFilter.changeFromTime) {
    expressions.push(
      sql.comparisonPredicate(
        sql.identifier([
          'ec1',
          'changed_at'
        ]),
        '>=',
        sql.raw('to_timestamp($1)', [
          Math.round(eventFilter.changeFromTime / 1000)
        ])
      )
    );
  }

  if (eventFilter.changeToTime) {
    expressions.push(
      sql.comparisonPredicate(
        sql.identifier([
          'ec1',
          'changed_at'
        ]),
        '<=',
        sql.raw('to_timestamp($1)', [
          Math.round(eventFilter.changeToTime / 1000)
        ])
      )
    );
  }

  return sql.booleanExpression(expressions, 'AND');
};

[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

The doc link is now https://github.com/gajus/slonik#slonik-query-building-sql-join

[pke]

This was driving me crazy! ;) sql.booleanExpression is gone. The docs should be updated.

[gajus]

Documentation does not mention booleanExpression anywhere.

I cannot account for all articles that have been published. I appreciate @baerrach adding a comment to alert others though.

[pke]

@gajus I was reading this article from you, from August 2019, when I was referring to "the docs". Maybe you could update it somehow?

btw: I am using your sql tagged template with alasql at the moment, and it works great! Thanks for your work

[ivan-kleshnin]

If some good soul develops a Slonik-compatible version of:

https://github.com/titarenko/knex-filter https://github.com/joostvunderink/knex-filter-loopback

Please let us know. Manual construction of such conditions is not scalable. Leaving this note here to be shown in search