CODEWITHSUNDEEP
doctrine-ormdoctrinedoctrine-query
Noobie
Noobie

Reputation: 99

How to use CASE and SUBSTRING_INDEX in doctrine query?

I have a sql query which is succesfully running in the mysql server and got the output.

But I couldn't able to convert this query in the doctrine format. Query is as below

SELECT (CASE WHEN seqnum < 10 THEN domain ELSE 'Others' END) as domain,
       SUM(c)
FROM (SELECT SUBSTRING_INDEX(SUBSTR(email, INSTR(email, '@') + 1), '.', 1) as domain,
             COUNT(*) as C,
             ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM newsletter_recipient
      WHERE LENGTH(email) > 0
      GROUP BY domain
     ) d
GROUP BY (CASE WHEN seqnum < 10 THEN domain ELSE 'Others' END)
ORDER BY SUM(c) DESC;

When i am using this in doctrine it give errors like

Expected known function, got 'SUBSTRING_INDEX'

Hope someone could help me to convert this query in doctrine format.

Upvotes: 0

Views: 501

Answers (1)

pestaa
pestaa

Reputation: 4749

You either need to implement vendor-specific functions so that DQL can translate it into proper SQL calls, or in this simple case a combination of built-in function calls might be enough:

SUBSTRING(email, LOCATE('@', email) + 1, ...

For a full list of available cross-platform functions, see the docs.

Also I cannot resist to mention that the domain name without the TLDs might contain dots, for example you might look for mail.example in [email protected]. Up to your specifications, though.

Upvotes: 1

Related Questions