ORM using SELECT CASE ...

[czcpf]

Hello friends,

Does the capability exist to use CASE inside select in ORM ? Example MySQL

select id,
(
CASE
WHEN qty_1 <= '23' THEN price
WHEN '23' > qty_1 && qty_2 <= '23' THEN price_2
WHEN '23' > qty_2 && qty_3 <= '23' THEN price_3
WHEN '23' > qty_3 THEN price_4
ELSE 1
END) AS total
from product;​

[yuri]

Hi, See https://github.com/espocrm/espocrm/b...ssion.php#L195. Plus it's documented in the complex expressions article. But I recommend using PHP wrappers rather then complex expression strings.

[czcpf]

Thanks for that! I must have missed that update in 7.4. Per your recommendation, can you explain what you mean by "PHP wrappers" ?

[yuri]

The Expression class with static methods, one that I linked above. When using IDE, autocompletion will help the syntax.

[czcpf]

Also, not pertaining to this topic but I'm curious as to why alias does not work in ORM 'having' ? Example,

$overdueSelection =Selection::fromString('COUNT:(id)');​

$this->entityManager->getQueryBuilder()
    ->select([
        $overdueSelection->withAlias('overdue'),
    ])
   ->from('SomeEntity')
    ->having([
        $overdueSelection->getExpression()->getValue() => 1
    ]) //WORKS.  Results in `HAVING COUNT(id) = 1` appended to SQL
    ->having([
        'overdue'=> 1
    ]) //NOT WORKS. Results in `HAVING 0​` appended to SQL 

[yuri]

It's how it implemented. Aliases are not allowed in WHERE in standard SQL, but allowed in HAVING (at least in MySQL). The having implementation in ORM uses WHERE implementation. If we can't use aliases in WHERE, it's OK not to use them in HAVING as well. It's even more consistent.