Self-Relation Column and "select"

[demogar]

Morning.

I just tried to create a self-related column (Category) like so:

'parent_category' => array(
            'title' => 'Parent Category',
            'relationship' => 'parent_category',
            'select' => "(:table).name"
        )

and the column is blank. Model looks like the following:

class Category extends \Eloquent {

    public static $table = 'categories';
    public static $timestamps = true;

    public function profiles()
    {
        return $this->has_many_and_belongs_to('Metadata', 'categories_users_metadata');
    }

    public function parent_category()
    {
        return $this->belongs_to('Category', 'parent_id');
    }

    public function child_categories()
    {
        return $this->has_many('Category','parent_id');
    }

}

Am I missing something?

[janhartigan]

Hey @demogar thanks for posting this issue. I actually have never tested it with self-relation columns. I definitely will now, though! I'm going to see if I can't find some time later tonight to test out your issue and come up with a fix.

[gilr]

I think I understand the problem: the used SQL request is the following one:

SELECT DISTINCT categories.id, categories.*, (SELECT name FROM categories WHERE categories.parent_id = categories.id) AS parent_category FROM categories GROUP BY categories.id ORDER BY categories.id ASC LIMIT 12 OFFSET 0

As here the local and foreign table is the same, the where clause is 'categories.parent_id' = 'categories.id'... Of course never found.

A correction could be to always force the use of an alias (This request works):

SELECT DISTINCT categories.id, categories.*, (SELECT name FROM categories AS foreign_table WHERE categories.parent_id = foreign_table.id) AS parent_category FROM categories GROUP BY categories.id ORDER BY categories.id ASC LIMIT 12 OFFSET 0

[janhartigan]

Ah ha! Good catch, @gilr! You saved me a bunch of time. I'm going to try to come up with a good solution to this issue. Will let you guys know soon!

[demogar]

Thanks a lot for all feedback guys. I will try to come up with a solution (if I find how this query is constructed 😄). I'm looking forward to hearing back about this issue.

Thanks.

[janhartigan]

Hey guys I just pushed this commit:

f4e26e7

For some reason it isn't attaching to this issue, but that commit should solve this problem. Along the way, I also added a nice feature where Administrator recognizes if something is a self-relationship, and as such will update the filter/edit options for that relationship after you save or delete an item. So your self-relationship will always stay up to date with the latest options. Let me know if you guys find any issues!