Geek Answers Handbook

Using a “When” in a Doctrine Select Statement

I have a selection request that I would like to execute using Doctrine:

$resultset = Doctrine_Query::create() ->select("t.code, t.description, case when t.id_outcome = 1 then 1 else 0 end as in_progress") ->from('LuOutcome t') ->orderBy('t.rank') ->fetchArray();

And he locks the "case." The documentation does not mention that this is possible (or not).

I wonder if the Doctrine lacks such an opportunity. If so, this is a pretty serious omission. Does anyone know about work?

+4
source share
5 answers

I had the same problem. My project is very old, and he tried to fix it quickly. So I just changed the code for Doctrine a bit, so I can use case when. This is my code for Doctrine 1.1.3.

Doctrine / Query.php, change the lines from 658 to 674:

  if (count($terms) > 1 || $pos !== false) { if($terms[0]=='case') { $terms=explode(" as ", $reference); $expression = array_shift($terms); $alias = array_pop($terms); if ( ! $alias) { $alias = substr($expression, 0, $pos); } $componentAlias = $this->getExpressionOwner($expression); $tableAlias = $this->getTableAlias($componentAlias); $expression=str_replace($componentAlias, $tableAlias, $expression); $index=0; $sqlAlias = $tableAlias . '__' . $alias; } else { $expression = array_shift($terms); $alias = array_pop($terms); if ( ! $alias) { $alias = substr($expression, 0, $pos); } $componentAlias = $this->getExpressionOwner($expression); $expression = $this->parseClause($expression); $tableAlias = $this->getTableAlias($componentAlias); $index = count($this->_aggregateAliasMap); $sqlAlias = $this->_conn->quoteIdentifier($tableAlias . '__' . $index); } $this->_sqlParts['select'][] = $expression . ' AS ' . $sqlAlias;

This is not a big change, but it helped me ...

+1
source

I had the same problem, and at first glance it seemed to have a workaround. I believe that you can “trick” the Doctrine Select parser by treating it as a subquery, enclosing it in parentheses.

Try:

 $resultset = Doctrine_Query::create() ->select("t.code, t.description, (case when t.id_outcome = 1 then 1 else 0 end) as in_progress") ->from('LuOutcome t') ->orderBy('t.rank') ->fetchArray();
+23
source

The BNF grammar for the Doctrine query language does not seem to contain anything related to the CASE construct.

+3
source

At one point in the doctrine, statements of action were apparently added: https://github.com/doctrine/orm-documentation/commit/189c729f15d2fafecf92662cad9553c2ec3dccd7#diff-0

+2
source

I recommend that you do not use this CASE syntax to solve this problem. It looks complicated.

Why don't you want

 $resultset = Doctrine_Query::create() ->select("t.code, t.description, t.id_outcome") ->from('LuOutcome t') ->orderBy('t.rank') ->fetchArray();

and then loop through the $ resultset and create this field (in_progress) manually depending on the value (id_outcome). You can use a small simple tiny method for this.

Benefits:

  • just
  • readable
-3
source

More articles:


All Articles