Doctrine 2 - ORM

CASE WHEN ... WHEN doesn't work

Details

  • Type: Bug Bug
  • Status: Reopened Reopened
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: 2.3.1
  • Fix Version/s: 2.4
  • Component/s: DQL
  • Security Level: All
  • Labels:
    None

Description

Having the following part in select DQL throws an exception.

SUM(CASE
            WHEN c.startDate <= :start THEN c.endDate - :start
            WHEN c.endDate >= :end THEN :end - c.startDate
            ELSE 0
            END)

exception:

[Syntax Error] line 0, col 124: Error: Expected Doctrine\ORM\Query\Lexer::T_ELSE, got '-'

It seems that it's failing inside the second THEN

This one also seems to fail:

SUM(CASE
            WHEN c.startDate <= :start THEN (c.endDate - :start)
            WHEN c.endDate >= :end THEN (:end - c.startDate)
            ELSE 0
            END)

exception:

[Syntax Error] line 0, col 60: Error: Unexpected '('

Another one:

SUM(CASE
                WHEN c.startDate <= :start THEN c.endDate - :start
                WHEN c.endDate >= :end THEN :end - c.startDate
                ELSE 0
                END) = :result FROM ...

exception:

[Syntax Error] line 0, col 60: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got '='

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Miha Vrhovnik added a comment -

I've added two more cases where the parsing fails. Do you want a separate tickets for that?

Show
Miha Vrhovnik added a comment - I've added two more cases where the parsing fails. Do you want a separate tickets for that?
Hide
Permalink
Fabio B. Silva added a comment -

Don't worry, I'll spend some time over this...
But I'm not sure about the last one.

Show
Fabio B. Silva added a comment - Don't worry, I'll spend some time over this... But I'm not sure about the last one.
Hide
Permalink
Miha Vrhovnik added a comment - - edited

The 3rd case seems work just fine as a part of a HAVING clause.
I haven't tried it but It might be that it fails with something simpler like SELECT COUNT( * ) = :foo FROM ... or SELECT COUNT( * ) = 2 FROM ...

Show
Miha Vrhovnik added a comment - - edited The 3rd case seems work just fine as a part of a HAVING clause. I haven't tried it but It might be that it fails with something simpler like SELECT COUNT( * ) = :foo FROM ... or SELECT COUNT( * ) = 2 FROM ...
Hide
Permalink
Miha Vrhovnik added a comment - - edited

Fabio I have two more...
It doesn't like NULL and subselect after then part

 
->addSelect('CASE
    WHEN po.quantity IS NULL THEN NULL
    ELSE po.quantity -
            COALESCE(0, (
                SELECT COUNT(rd.product) FROM xxxx rd
                    WHERE (rd.startDate <= :end) AND (rd.endDate >= :start) AND
                        rd.product = c.product)))
    END
    AS po.quantity
')

:edit replaced with real query

Show
Miha Vrhovnik added a comment - - edited Fabio I have two more... It doesn't like NULL and subselect after then part ->addSelect('CASE WHEN po.quantity IS NULL THEN NULL ELSE po.quantity - COALESCE(0, ( SELECT COUNT(rd.product) FROM xxxx rd WHERE (rd.startDate <= :end) AND (rd.endDate >= :start) AND rd.product = c.product))) END AS po.quantity ') :edit replaced with real query
Hide
Permalink
Miha Vrhovnik added a comment -

addon: well the subquery part can be full query with joins ....

Show
Miha Vrhovnik added a comment - addon: well the subquery part can be full query with joins ....

People

  • Assignee:
    Fabio B. Silva
    Reporter:
    Miha Vrhovnik
Vote (0)
Watch (2)

Dates

  • Created:
    Updated: