Complex HAVING & WHERE queries #491
Description
Complex condition like
$users = $this->orm->authors->findBy([
ICollection::OR,
['books->title' => 'Book 1'],
[
CompareSmallerThanEqualsFunction::class,
[CountAggregateFunction::class, 'tagFollowers->tag'],
2,
],
]);creates an invalid SQL:
SELECT `authors`.*
FROM `authors` AS `authors`
LEFT JOIN `books` AS `books` ON (`authors`.`id` = `books`.`author_id`)
LEFT JOIN `tag_followers` AS `tagFollowers` ON (`authors`.`id` = `tagFollowers`.`author_id`)
GROUP BY `authors`.`id`
HAVING ((`books`.`title` = 'Book 1')) OR (COUNT(`tagFollowers`.`tag_id`) <= 2)Db server doesn't know the books.title value since it is not aggregate in having.
Doing aggregation is not possible here, so we have to employ complex query rewrite:
- put non-aggregated condition to join
- select the joined table PK column
- compare the selected column to not null
which will produce something like:
SELECT `authors`.*, `books`.`id` AS `_booksid`
FROM `authors` AS `authors`
LEFT JOIN `books` AS `books` ON (`authors`.`id` = `books`.`author_id` and `books`.`title` = 'Book 1')
LEFT JOIN `tag_followers` AS `tagFollowers` ON (`authors`.`id` = `tagFollowers`.`author_id`)
GROUP BY `authors`.`id`
HAVING (`_booksid` IS NOT NULL) OR (COUNT(`tagFollowers`.`tag_id`) <= 2)Failing testcase: https://github.com/nextras/orm/tree/complex_having_where_aggregation_condition