CODEWITHSUNDEEP
mysqlsymfonydoctrine
Louwki
Louwki

Reputation: 715

IF Statement in order by clause of Doctrine 2 query Symfony 2

Im trying to do an blog that shows all the comments and user will be able to post comments on reply on previously posted comments no to order then in the sql query so they will show up in order i keep getting the error.

SELECT c.userid, c.comment, c.reply, u.username, c.createdat, c.image, c.username as
ComUser FROM Bundle:Comments c LEFT JOIN Bundle:Users u WITH c.userid = u.id
WHERE c.articleid = 1 ORDER BY (CASE WHEN (c.parentid = 0) THEN c.id ELSE c.parentid END) 
c.createdat ASC

Gives Error: [Syntax Error] line 0, col 515: Error: Expected end of string, got 'CASE'

even tried normal way 

ORDER BY IF(c.parentid = 0, c.id, c.parentid), c.createdat ASC

Gives Error: [Syntax Error] line 0, col 515: Error: Expected end of string, got '('

When i use the normal ORDERY BY IF() in normal sql code (not doctrine) it works fine.

Upvotes: 4

Views: 8440

Answers (2)

Louwki
Louwki

Reputation: 715

Awsome!!! Thanks for your help guys i just got it!

My Solution:

SELECT c.userid, c.comment, c.reply, u.username, c.createdat, c.image, c.username as
ComUser, (CASE WHEN (c.parentid = 0) THEN c.id ELSE c.parentid END) as OrderByComm
FROM Bundle:Comments c LEFT JOIN Bundle:Users u WITH c.userid = u.id
WHERE c.articleid = 1 ORDER BY OrderByComm, c.createdat ASC

I did the order by calculation in the select part.

Upvotes: 3

Matt Busche
Matt Busche

Reputation: 14333

looks like you just have a typo. Your overall syntax is correct.

ORDER BY CASE WHEN (c.parentid = 0) THEN c.id ELSE .parentid END c.createdat ASC

should be

ORDER BY CASE WHEN (c.parentid = 0) THEN c.id ELSE c.parentid END, c.createdat ASC

Upvotes: 6

Related Questions