setModel problem

[Sorina Ifrim]

Hello,

I didn't managed to find an answer for a situation that I deal with. I have a model User with id, name, parent_id (= fk in the same table) and I need to set in grid all the users and their parents. The select used in mysql is:
SELECT a.`name`, (select `name` from `user`  b where a.`parent_id`=b.`id`) `parent `FROM `user` a
If I use setModel ('User', array('name','parent'))  parent field is null.

Can you please tell how should I do ?

Thank you!

Sorina

[Willem Rheeder]

Hi Sorina,  

have you tried to add a 

$this->hasOne('User','parent_id','name');

to your model?  I had a similiar case and found solution under following post 

Self Referencing models ATK4

Regards,

Willem

[Sorina Ifrim]

Unfortunately it doesn't solve my problem. The sql generated is like this:
select  `name`,(select `name` from `user` where `user`.`parent_id` = `user`.`id` ) `parent`  from `user`
As far as I know I need alias tables for the select to work.

[Willem Rheeder]

Try this,  

class model_user extends Model_Table {

    public $table_alias='user_c';

    public $entity_code = 'user';

    function init() {

        parent::init();

        //$this->debug();

        $this->addField('name');

        $this->hasOne('ParentUser', 'parent_id', 'name');

    }

}

class model_ParentUser extends Model_Table{

    public $table_alias='user_p';

    public $entity_code = 'user';

    function init() {

        $this->addField('name');

        parent::init();

    }

}

[Sorina Ifrim]

Hei,

It's working this way, thank you very much for your help!

[Miss Khushbu]

Hi,

Instead of defining two separate classes for same table with all fields is not a good idea...

Can't it be done like 

class Model_User extends Sql_Model {

public $table_alias ='a';

function init(){

parent::init();

...addFields

 $this->hasOne('ParentUser', 'parent_id');

$this->hasMany('User', 'parent_id',null,'Children');

}

}

class Model_ParentUser extends Model_User{

public $table_alias='b';

}

Hope you got idea ... 

[Sorina Ifrim]

Hi,

Thank you for the idea, it's great!