Advanced query with kohana ORM

Question

If you know my tables looks like these

[category]
-category_id
-lft
-rgt
....

[catinfo]
-catinfo_id
-category_id
-lang_id
-name
....

[lang]
-lang_id
-language

How can I rewrite this query in Kohana ORM?

SELECT node.category_id, catinfo.name, COUNT("parent.category_id") - 1 AS depth
        FROM category AS parent, category AS node
        LEFT JOIN catinfo
            ON catinfo.category_id = node.category_id
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND catinfo.lang_id = 1
        GROUP BY node.category_id
        HAVING depth <= 3
        ORDER BY node.lft

I don't have any clue how to start with it.

Answer 1

Better not write such a complex query with the ORM and just use the query builder or normal queries.

The query will probably look something like this if you use the query builder:

DB::select(
    'node.catergory_id', 
    'catinfo.name', 
    array(DB::expr('COUNT(`parent.category_id`) - 1'), 'depth')
)   
    ->from(array('category', 'parent'), array('category', 'node'))
    ->join('catinfo', 'LEFT')
    ->on('catinfo.category_id', '=', 'node.category_id')
    ->where('node.lft', 'BETWEEN', array('parent.lft', 'parent.rgt'))
    ->and_where('catinfo.lang_id', '=', 1)
    ->group_by('node.category_id')
    ->having('depth', '<=', 3)
    ->order_by('node.lft');