Need Ideas On How I Might Solve Problem Of Assigning User/role

Yii 1.1 General Discussion
1

I have task list object that I want to be able to assign to a user or a group of users (role)

Table Task

(id number not null auto increment primary key

assigned_to number

task varchar(200) not null

);

Table role

(id number primary key

, role varchar(20)

);

Table User

(id number primary key

,name varchar(40)

.role_id number

);




select role_id, 2 sort, name from user 

union

select id, 1, role from role 

order by 1, 2;

This will produce a list sorted by role




role_id, sort, name

 1 | 1 | Admin

 1 | 2 |  Mike

 2 | 1 | Executive

 2 | 2 |  Pete

 2 | 2 |  Sue

 3 | 1 | Manager

 3 | 2 |  Mary

 3 | 2 |  Bob

 3 | 2 |  Tim

 4 | 1 | Clerk

 4 | 2 |  Tom

 4 | 2 |  Sally

I need the user to create a task and assign a user or a role to the task.

Not sure how to do this with CHtml::listData()

Should I use both a assigned_to_user and assigned_to_role fields ? Hidden?

Not sure how to get started.

Thanks for any advice !

2

[size=“2”]Not sure if you’re looking for a way to structure it or to query it. Would this work?[/size]





-- 

-- Structure for table `role`

-- 


DROP TABLE IF EXISTS `role`;

CREATE TABLE IF NOT EXISTS `role` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 

-- Structure for table `task`

-- 


DROP TABLE IF EXISTS `task`;

CREATE TABLE IF NOT EXISTS `task` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `task` varchar(255) NOT NULL,

  `entity_name` varchar(255) NOT NULL,

  `entity_id` int(11) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 

-- Structure for table `user`

-- 


DROP TABLE IF EXISTS `user`;

CREATE TABLE IF NOT EXISTS `user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `role_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `role_id` (`role_id`),

  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I’ve mapped the type of entity (User, Role etc.) and the entity Id (User Id, Role Id) in the task table. That way, you can query all tasks for Roles and/or Users.





// Table user

'tasks' => array(self::HAS_MANY, 'Task', 'entity_id', 'on' => 'entity_name=\'user\'')


// Table role

'tasks' => array(self::HAS_MANY, 'Task', 'entity_id', 'on' => 'entity_name=\'task\'')

Matt

3

Thanks Matt !

That is certainly a valid way to start.

I was toying with using the role id concatenated with user id to a number (eg. 1.2, 2.2, 3.1…)

I was not certain how to format the list using a CHtml::listData because I need to concatenate columns. I thought maybe

I might use an alias (getter) column in the model for user.


 

     public function getEntityId()

    {

	return $this->role_id.'.'.$this->id;

    }

I could also do something similiar for the role model. Return role.id .’.0’ and the role name.




      <div class="row">

            <?php echo $form->labelEx($model,'entity_id'); ?>

            <?php echo $form->dropDownList($model, 'entity', CHtml::listData(User::model()->findAll(),'entityId', 'displayname'), array('prompt' => 'None')); ?>

            <?php echo $form->error($model,'entity_id'); ?>

      </div>

Also, I am not clear on how to grab the list when presenting it to the user in the task form.

Can I use AR or do I need to do a SQL query? How do I incorporate both rule and user in the above fetch?

4

I am not sure I understand the mapping.




// Table user

'tasks' => array(self::HAS_MANY, 'Task', 'entity_id', 'on' => 'entity_name=\'user\'')


// Table role

'tasks' => array(self::HAS_MANY, 'Task', 'entity_id', 'on' => 'entity_name=\'task\'')

Should the \‘task\’ be role?

I also wonder how the entity_id would map to both user and role. Technically there could be a user 1 and role 1.

Am I missing something?

5

Yes, sorry, it should be


'tasks' => array(self::HAS_MANY, 'Task', 'entity_id', 'on' => 'entity_name=\'role\'')

If a task can belong to a user and a role then you’d need to use MANY-MANY; same idea though, but linked through a join table. Ie. the join table, task_entity, would contain taskId, entity_name, entity_id

Matt

6

Heading to bed now but I’ll try to code something up tomorrow.

Matt

7

Thanks Matt

I have it coded pretty much like your suggestion.

Only issue is the drop down list — how to get the correct entity or role to show.

The user model (uses with (role) to include the role but it doesn’t show role names only users)

8

I can get users or roles but not both.

I want something like this, but need to create a unique id for a key.

role_id, sort, name

1 | 1 | Admin

1 | 2 | Mike

2 | 1 | Executive

2 | 2 | Pete

2 | 2 | Sue

3 | 1 | Manager

3 | 2 | Mary

3 | 2 | Bob

3 | 2 | Tim

4 | 1 | Clerk

4 | 2 | Tom

4 | 2 | Sally

Possibly add user_id in the select or concatenate user_id with role_id.

Anyone have a suggestion.

How about a view? I am trying a bunch of things but seem to keep hitting walls!

9

Attached a demo. The coding is pretty ugly but I think it does what you need.

I couldn’t include the framework folder so just update the index.php and confg.php.

Matt

4316

task.zip

10

Thank you Matt !

I will take a look and let you know if I have questions. I appreciate your taking the time to help!

Bryan