0

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.

Moe
  • 130
  • 5

1 Answers1

0

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');
Manuras
  • 506
  • 2
  • 10
  • why should i prefer to use the query builder for this and not just use DB::query()? – Moe Sep 24 '13 at 18:26
  • 2
    It's due to personal preference. Advantages of using the query builder is that it does the identifier and value quoting for you. Another advantage (which maybe doesn't apply for this use-case) is that if you are using the query builder you can dynamically alter your query. For instance you want to add a limit, but only want it to be added if a certain parameter was set. This is done much more elegant with a query builder than with regular queries. – Manuras Sep 24 '13 at 22:55