Description
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.