cerating several rows on a MANY_MANY table

[font="Arial"]question for a (very) newby guy :

I’ve searched in the forum but I’m affraid my knowledges are not enough to gather bits and parts of things seen :-/

I’ve 2 models (let say : users and access) on which I generated the CRUD pages from yiic shell.

They are linked together with a MANY_MANY relation and a 3rd model (users_has_access) has been created in which I have userId and accessId attributes.

I saw that it was impossible to generate the CRUD pages for relation/association tables as they have composite keys (I’m under yii 1.0.9)

my problem is :

when I create (or update) a particular user, I’d like to be able (in the same users/_form) to add/modify the accesses a user has access to,

a “nice” solution would be to generate one chexkbox for each acces but I’m not sure how to do it and, above all, get the right values back and save them in users_has_access

a other solution that would be fine is to have crud like features to "brute"force" entries in the users_has_access model

any help or advice on this ? ???

thanks for your help

[/font]

Hi! I found the very same problem you’ve struggling on. The default forum search is quite awful but I’ve managed to find a couple of post related to the relational table in MANY_MANY relations

The posts are this and that.

As you can read 1.1 version of Yii is going to take care of relation tables out of the box.

Anyway I used a more rude and newbie solution to this problem and implemented it this way.

I’ve got the two tables table_1 and table_2 with their own PK table_1.id and table_2.id with their own relationship MANY_MANY table called table_1_2 with one PK composed by two FK called table_1_2.table_1_id and table_1_2.table_2_id.

Then I’ve created a model for all the three tables that resulted in the classes Table_1 Table_2 and Table_1_2.

In the class Table_1 I’ve added the relevant relation:


    public function relations() {

        return array(

            'table2ids' => array(self::MANY_MANY, 'Table_2', 'table_1_2(table_1_id, table_2_id)'),

        );

    }

this way you can get the array of the models of table_2 related to table_1 with $table1model->table2ids

since I wanted to have a checkbox in the form for Table_1, I had to add this function in the Table_1Controller:


    public function getTable2Ids($model=null) {

        $ids=array();

        if ($model) {

            foreach($model->table2ids as $table2)

                $ids[$table2->id]=$table2->id;

        } else {

            $_model = Table_2::model()->findAll();

            foreach($_model as $table2)

                $ids[$table2->id]=$table2->id;

        }

        return $ids;

    }

consider that this code has to be fine tuned to get the relevant names you’ll need, since my PKs on the two tables were names, not numeric ids.

now in views/table_2/_form.php I can write:


    <div class="simple">

        <?php echo CHtml::activeLabelEx($model,'table2ids'); ?>

        <?php echo CHtml::checkBoxList('Table_1[table2ids]', $this->getTable2Ids($model), $this->getTable2Ids())?>

    </div>

at the moment this is styled very badly and I still have to sort it out… anyway…

now I had to introduce a method in the Table_1 model to save and update everything in the table_1_2 as it should, this can be done with this piece of code:


    public function updateTable2ids() {

        if ($this->isNewRecord) {

            // if the entry is new add all the couples to the table_1_2 table

            // it *SHOULD* be safe to insert them without checking against the referenced tables

            foreach($this->table2ids as $table2id) {

                $table_1_2 = new Table_1_2();

                $table_1_2->table_1_id = $this->id;

                $table_1_2->table_2_id = $table2id;

                $table_1_2->save();

            }

        } else {

            // if it's an update find the entries that need to be updated, leave the others untouched and remove the unused

            $_models_arr = Table_1_2::model()->findAll('table_1_id=:table_1_id',array(':table_1_id'=>$this->id));

            $_old_2ids = array();

            foreach($_models_arr as $_model) {

                $_old_2ids[] = $_model->id;

            }

            // $to_be_added contains all the values in $this->models not present in $_old_types

            $to_be_added = array_diff($this->models, $_old_2ids);

            // $to_be_removed contains all the values in $_old_types that needs to be wiped out

            $to_be_removed = array_diff($_old_2ids, $this->models);

            /*

             * FIXME should find a way to reinizialize all the relations to the original values

             * since when the form is re-displayed all the entries should be in place

             */

            // there's nothing to be updated... probably the user is wrong <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/tongue.gif' class='bbc_emoticon' alt=':P' />

            if (count($to_be_added) == 0 && count($to_be_removed) == 0)

                return false;

            foreach($to_be_removed as $table2id) {

                $_models_arr[array_search($table2id, $_old_2ids)]->delete();

            }

            foreach($to_be_added as $table2id) {

                $table_1_2 = new Table_1_2();

                $table_1_2->table_1_id = $this->id;

                $table_1_2->table_2_id = $table2id;

                $table_1_2->save();

            }

        }

        return true;

    }

    public function afterSave() {

        // used only when creating the object

        $this->updateTable2ids();

        return parent::afterSave();

    }

the new function is then called automatically thanks to afterSave() when creating a new entry in table_1 and in the Table_1Controller I’m calling it explicitly before saving in the actionUpdate() method

I hope I didn’t miss anything.

If you or anyone else have other idea for improvements, it’s welcome…

Cya

hi Peach , thanks alot for spending time on this :slight_smile:

helped me a lot! almost working (the display is ok) yet I’m not able to save/update records :(

actualy I don’t understand why you said “I’m calling it explicitly before saving in the actionUpdate() method” cause I thought, aftersave is automatically called when save() is called (right?) so this (should) be done for both actionCreate and action Update ?

if not , how do you call it explicitely ?

thanks again

:)

unfortunately I didn’t find the reason why on update the function wasn’t called too, that mean that I had to call it manually in actionUpdate() this way:


    public function actionUpdate() {

        $model=$this->loadTable_1();

        if(isset($_POST['Table_1'])) {

            $t2Before = $model->table2ids;

            $model->attributes=$_POST['Table_1'];

            if ($t2Before === $model->table2ids)

                // NOTE if the has been no assignation reinitialize the array

                $model->table2ids = array();

            // update the table 2 ids relations

            $model->updateTable2ids();

            if($model->save())

                $this->redirect(array('show','id'=>$model->ID));

        }

        $this->render('update',array('model'=>$model));

    }

tell me if you can find why :D