mirror of
https://github.com/doctrine/orm.git
synced 2026-03-24 06:52:09 +01:00
DDC-1858: LIKE and IS NULL operators not supported in HAVING clause #2338
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Originally created by @doctrinebot on GitHub (Jun 7, 2012).
Originally assigned to: @guilhermeblanco on GitHub.
Jira issue originally created by user abhoryo:
The LIKE and IS NULL operators are not supported in HAVING clause.
Work:
SELECT *a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY *a HAVING uuuu in (3,6)
SELECT *a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY *a HAVING uuuu = 3
SELECT *a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY *a HAVING uuuu >= 3
...
Don't work:
SELECT *a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY *a HAVING uuuu LIKE 3
SELECT *a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY *a HAVING uuuu IS NULL
SELECT *a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY *a HAVING uuuu IS NOT NULL
@doctrinebot commented on GitHub (Jun 8, 2012):
Comment created by @ocramius:
I think this has already been fixed in latest master and 2.1.7. Could you just give it a try and eventually confirm?
@doctrinebot commented on GitHub (Jun 8, 2012):
Comment created by abhoryo:
Already try with 2.17, 2.20 and 2.2.2. This hasn't been fixed.
@doctrinebot commented on GitHub (Jul 4, 2012):
Comment created by bdiang:
I'm also having this issue (2.2.2). Is there any workaround for this?
Column aliases also are not supported in HAVING clause:
Above code causes error "FieldCount is not pointing to class" and IS NULL causes "Expected =, <, <=, <>, >, >=, !=, got 'IS'"
@doctrinebot commented on GitHub (Aug 29, 2012):
Comment created by @beberlei:
Its not a bug as the EBNF says that this is not possible.
[~guilhermeblanco] Is this something we should support or not?
@doctrinebot commented on GitHub (Aug 29, 2012):
Comment created by stof:
Another place where it is not supported is in the CASE clause.
I would vote +1 for supporting it
@doctrinebot commented on GitHub (Jun 10, 2013):
Comment created by bitone:
@Benjamin Eberlei
The EBNF seems to indicate that any search condition is valid on the HAVING clause:
http://savage.net.au/SQL/sql-99.bnf.html#having%20clause
If you look at the Search condition ( http://savage.net.au/SQL/sql-99.bnf.html#search%20condition ), the NULL predictate ( http://savage.net.au/SQL/sql-99.bnf.html#null%20predicate ) seems to be a part of it.
Maybe I'm misinterpreting the BNF ?
And if not, any idea of a targeted release for the fix ?
@doctrinebot commented on GitHub (Jun 11, 2013):
Comment created by @guilhermeblanco:
[~beberlei] it seems to be SQL-92 compatible. The improvement is valid.
We should support it. I'll take a look into this. =)
Cheers,
@doctrinebot commented on GitHub (Jun 11, 2013):
Comment created by @guilhermeblanco:
Functionality is already implemented in master as per coverage added in here:
4e99c5c127Closing the ticket.
@doctrinebot commented on GitHub (Aug 14, 2013):
Comment created by litz:
At the moment, only the
HAVING field IS [NOT] NULLis working.The
HAVING field LIKEas in(SELECT *a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY *a HAVING uuuu LIKE 3)
still does not work.
@doctrinebot commented on GitHub (Aug 19, 2013):
Comment created by @guilhermeblanco:
I implemented support for ResultVariable in LikeExpression as of
43fc8bafa7@doctrinebot commented on GitHub (Aug 19, 2013):
Comment created by litz:
It works. Thanks !
@doctrinebot commented on GitHub (Sep 8, 2013):
Comment created by @beberlei:
Assigned fix version
@doctrinebot commented on GitHub (Sep 8, 2013):
Issue was closed with resolution "Fixed"
@doctrinebot commented on GitHub (Oct 6, 2014):
Comment created by the_storm:
[~guilhermeblanco] Please backport to released version. This is very serious issue, especially when you make search queries.
@doctrinebot commented on GitHub (Oct 6, 2014):
Comment created by @ocramius:
[~the_storm] this is an improvement, not a bug fix, therefore there will be no backporting.
@doctrinebot commented on GitHub (Oct 7, 2014):
Comment created by the_storm:
[~ocramius] Then what workaround I can use? Lets say I do leftJoin on table and do AVG(object.value) > 3 OR object IS NULL. I.e. I want all results that have avg value higher than 3 and also results that do not have object at all?
@doctrinebot commented on GitHub (Oct 7, 2014):
Comment created by @ocramius:
The current workaround is bumping the ORM version, which may or may not be a problem depending on your stability policies and how much you need this feature.
Otherwise, NativeSQL until the functionality is available in a stable release.
@doctrinebot commented on GitHub (Oct 7, 2014):
Comment created by the_storm:
[~ocramius] Not an option for the project. However I found workaround that I will share here in case someone come here from google search like me.
$queryBuilder->addSelect( 'COALESCE(AVG(ra.value), 0) AS HIDDEN averageRating' ); $queryBuilder->leftJoin( 'c.ratings', 'ra' ); $queryBuilder->andHaving( 'averageRating >= :avgRating' ); $queryBuilder->orHaving( 'averageRating = 0' ); $queryBuilder->setParameter( ':avgRating', $ratingFilter );