How can I more organize this Laravel DB query?

I'm new to Laravel and I'm implementing product categories logic using Nested Set Model.

http://ift.tt/12pRuNw

The following query is to get immediate subordinates of a node and it's really hard for me to figure out how to organize this raw SQL query into simple and readable Laravel expressions using Laravel query builder. Any suggestion?

DB::select('SELECT node.name, 
(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth 
FROM categories AS node, categories AS parent, 
categories AS sub_parent, 
( SELECT node.name, (COUNT(parent.name) - 1) AS depth 
    FROM categories AS node, categories AS parent 
    WHERE node.lft BETWEEN parent.lft AND parent.rgt 
    AND node.id = :id GROUP BY node.name ORDER BY node.lft) AS sub_tree 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt 
    AND sub_parent.name = sub_tree.name 
GROUP BY node.name 
HAVING depth = 1 
ORDER BY node.lft', ['id'=>$this->attributes['id']]);

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/26zkWQy
via IFTTT