PHP Activerecord - Join on the same table

Question

I'm using php.activerecord and now I'm trying to to a join on the same table but somehow it doesn't work.

I want to achieve something like this:
http://sqllessons.com/categories.html

I have three main categories and n x subcategories. What I want to do is, to join this table (like in the example link) and achieve something like this:

ID     |     Category     |     is a Subcategory of
1      | Main 1           | NULL
2      | Main 2           | NULL
3      | Main 3           | NULL
4      | Sub 1            | Main 1
5      | Sub 2            | Main 2
6      | Sub 3            | Main 1

I've tried a simple join on the same table through the model

 static $has_many = array(
    array('category')
 );

and

$data = Category::all(array('joins' => array('category')));  

but I just get this error:

Fatal error: Uncaught exception 'ActiveRecord\DatabaseException' with message 'exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'rezepte_category'' in /home/www/xxxxx/xxxxx/classes/activerecord/lib/Connection.php on line 325    

Anyone an idea?

UPDATE 1:
Apparently I have to set an ALIAS. I did this:

    static $has_many = array(
    array('subcategory', 'class'=>'category','foreign_key' => 'id')
);  

But still the same error.

Answer 1

I think you also need a

static $belongs_to = array(array('category'));

in your model. If that does not help, you may want to try a custom sql join:

$join = 'LEFT JOIN category c ON(category.category_id = c.parent_id)';
$categories = Category::all(array('joins' => $join));

The documentation will provide further information: http://www.phpactiverecord.org/projects/main/wiki/Finders#joins