Missing primary filter lifting to HAVING clause

[hrach]

          I am a bit confused with separate WHERE and HAVING in some cases. For example these two produce identical query, which can't be right, as it only works with AND.

$this->model->books->findBy([
    ICollection::OR,
    ['title' => 'Book 1'],
    [CompareGreaterThanFunction::class, [CountAggregateFunction::class, 'tags->id'], 0],
]);
$this->model->books->findBy([
    ICollection::AND,
    ['title' => 'Book 1'],
    [CompareGreaterThanFunction::class, [CountAggregateFunction::class, 'tags->id'], 0],
]);

SELECT "books".* FROM "books" AS "books" 
LEFT JOIN "books_x_tags" AS "books_x_tags__COUNT" ON ("books"."id" = "books_x_tags__COUNT"."book_id") 
LEFT JOIN "tags" AS "tags__COUNT" ON ("books_x_tags__COUNT"."tag_id" = "tags__COUNT"."id") 
WHERE ((("books"."title" = 'Book 1'))) 
GROUP BY "books"."id", "books"."title" 
HAVING ((COUNT("tags__COUNT"."id") > 0));

Originally posted by @stepapo in https://github.com/nextras/orm/issues/685#issuecomment-2446199417

[hrach]

@stepapo Thanks for the thorough testing, fix in #687

[stepapo]

I really wish to tell you everything is ok :-D But this does not work in postgres: column "author.name" must appear in the GROUP BY. Not sure about creating new issue as it might be connected to this one, so just posting it here.

$this->model->books->findBy([
    ICollection::OR,
    ['author->name' => 'Writer 1'],
    [CompareGreaterThanFunction::class, [CountAggregateFunction::class, 'tags->id'], 0],
]);

[hrach]

@stepapo fixed, added more tests 🙏 🤞

[stepapo]

Found one more, hopefully not a big deal. Just like the first one, AND and OR produce identical query, only correct for AND.

$this->model->books->findBy([
    ICollection::OR,
    ['title' => 'Book 1'],
    [ICollection::AND, new NoneAggregator, 'tags->id' => 3],
]);
$this->model->books->findBy([
    ICollection::AND,
    ['title' => 'Book 1'],
    [ICollection::AND, new NoneAggregator, 'tags->id' => 3],
]);