CODEWITHSUNDEEP
phpsymfonydoctrine
Kamil P
Kamil P

Reputation: 784

Doctrine select count if

I have that query and want to translate it to doctrine

SELECT 
    ex.id,
    ex.title AS 'title of examination',
    SUM(IF(su.status_id = 2, 1, 0)) AS 'published surveys',
    SUM(IF(su.status_id = 1, 1, 0)) AS 'other surveys'
FROM
    examination ex
        LEFT JOIN
    survey su ON ex.id = su.examination_id
GROUP BY ex.id;

I know I can use $qb->select("[SQL_HERE]") but it wont be available to work with other databases, only my current(MySQL).

So, how can I encode this query to doctrine?

Upvotes: 1

Views: 1064

Answers (1)

Miro
Miro

Reputation: 1899

There is no pure Doctrine cross database solution, you can:

  1. Create your own Doctrine functions or use bundle for it (https://github.com/beberlei/DoctrineExtensions)

  2. Or you can change the database, for example add column published that could have value 0 or 1 (and value of this column could be updated by trigger, if you want to keep your original status_id column) and then you can use pure doctrine query:

    SELECT SUM(published) as count_published FROM ...

(similarly for other surverys you could use another column other ...)

Upvotes: 1

Related Questions