Converting sql to dql

Question

I have a problem converting a sql query to dql. Can anyone help me.

This is what i got so far:

$query = $this->em->createQuery("
        UPDATE MyBundle:Tree AS n
        JOIN MyBundle:Tree AS movingNode ON movingNode.id = :node
        JOIN MyBundle:Tree AS parentNode ON parentNode.id = :parent
        SET
        n.lft = n.lft +
            IF (parentNode.lft < movingNode.lft,
                 IF (n.lft >= movingNode.rgt + 1, 0,
                        IF (n.lft >= movingNode.lft, parentNode.lft - movingNode.lft + 1,
                                IF (n.lft >= parentNode.lft + 1, movingNode.rgt - movingNode.lft + 1 , 0
                                    )
                            )
                     ),
                 IF (n.lft >= parentNode.lft + 1, 0,
                        IF (n.lft >= movingNode.rgt + 1, -movingNode.rgt + movingNode.lft - 1,
                                IF (n.lft >= movingNode.lft, parentNode.lft - movingNode.rgt, 0
                                    )
                            )
                     )
                ),
        n.rgt = n.rgt +
            IF (parentNode.lft < movingNode.lft,
                 IF (n.rgt >= movingNode.rgt + 1, 0,
                        IF (n.rgt >= movingNode.lft, parentNode.lft - movingNode.lft + 1,
                                IF (n.rgt >= parentNode.lft + 1, movingNode.rgt - movingNode.lft + 1 , 0
                                    )
                            )
                     ),
                 IF (n.rgt >= parentNode.lft + 1, 0,
                        IF (n.rgt >= movingNode.rgt + 1, -movingNode.rgt + movingNode.lft - 1,
                                IF (n.rgt >= movingNode.lft, parentNode.lft - movingNode.rgt, 0
                                    )
                            )
                     )
                )
        WHERE parentNode.lft < movingNode.lft OR parentNode.lft > movingNode.rgt;
    ")->setParameter('node', $node)->setParameter('parent', $parent);

It seems like i need to split it into subquerys or in more querys. Iam not shure.

Also it seems like there isent support for update and join in dql.

I don't believe Doctrine can handle `if` cases like you want. I would either use ORM (i.e. your `Tree` entity instances, through their getters/setters, but it's not a flexible as native SQL for this scenario) or native SQL as described here: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/native-sql.html — Stock Overflaw, Dec 12 '16 at 15:34
Have a look to nested set implementations in doctrine2 : https://wildlyinaccurate.com/simple-nested-sets-in-doctrine-2/ — Weenesta - Mathieu Dormeval, Dec 12 '16 at 15:48

score 0 · Accepted Answer · edited May 23 '17 at 12:09

You can use CASE instead of IF or you could create your own custom IF DQL function. If you did a bit of searching you probably could have already found your answer.

Here's a post that describes those: Converting MySQL to Doctrine Query Builder. Issues with IF and CONCAT. Or another approach for subqueries on select

Also, using UPDATE in your Doctrine query doesn't seem like the right thing to do since normally Doctrine takes care of updates. You may want to reconsider your design.

Converting sql to dql

1 Answers1