Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for "HAVING" clause #48

Open
techniq opened this issue Dec 9, 2015 · 0 comments
Open

Add support for "HAVING" clause #48

techniq opened this issue Dec 9, 2015 · 0 comments

Comments

@techniq
Copy link
Member

techniq commented Dec 9, 2015

I'm currently using a custom query with subquery selects, and have the need to filter on these extra fields in the query (ratings_average and ratings_count in my example). For these to work, they must be applied using HAVING instead of WHERE as the filtering must occur after the subqueries have executed (from my understanding).

  ...
  let filterQuery = db.sql.filterQuery(models.Event, this.query, options);

  var ratingsAverage = db.knex.raw('(select avg(rating) from review where event_id = event.id) ratings_average');
  var ratingsCount = db.knex.raw('(select count(rating) from review where event_id = event.id) ratings_count');
  filterQuery.select(ratingsAverage, ratingsCount);

  if (this.query.having) {
    for (var field in this.query.having) {
      for (var op in this.query.having[field]) {
        filterQuery.having(field, op, this.query.having[field][op]);
      }
    }
  }

  let entities = yield db.sql.findAll(models.Event, filterQuery, options);

My hacked code works for all the basic operators, but I would implement the same logic as the where is currently (handling OR'ing, NULLs, etc.)

Since this would increase the query API further, and is mostly specific to js-data-sql, I wanted to discuss if this is something we want to add.

Related side note: I'd like to come up with a way to define the ratings_average and ratings_count on the resource (kind of like a computed property, but they are retrieved as part of the query and not from a individual entities properties.

@techniq techniq changed the title Add support for "having" clause Add support for "HAVING" clause Dec 9, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant