Insert to multiple tables

[hossj]

I was wondering if we have a table called user, and another table called group (admin, member, moderator, etc.) and when we insert from form into user we also want to add to another table called user_group where it defines under what group this specific user falls under meaning: table user_group id = AI user_id = 1 group_id = 2 (meaning it is a simple member)

How can I do this under the user_model.php?

[sethwhitaker]

You will want to use the has_many_pivot relationship.

 $this->has_many_pivot['user'] = array(
     'foreign_model'=>'Group_model',
     'pivot_table'=>'user_group',
     'local_key'=>'id',
     'pivot_local_key'=>'user_id', 
     'pivot_foreign_key'=>'group_id',
     'foreign_key'=>'id',
     'get_relate'=>FALSE
);

With this configuration you will be able to do $users = $this->user_model->with_group()->get_all(); for example and it will retrieve all users with their related group.

If your asking about how to insert into the pivot table, im not sure that can be done directly with the MY_Model setup, at least without writing extraneous Model classes for the pivot table directly, which I dont think should be done.

Heres an example in a recent project of mine.

// Game_model.php
function new_game($user_id){
    //Insert default values into Game table
    $this->insert(array(
         'whose_turn' => 0,
         'active'=>1
    ));

    //Grab the insert ID (game_id)
    $insert_id = $this->db->insert_id();

    // Manually insert a row into the pivot table 'game_user'
    $this->db->insert('game_user', array(
       'game_id'=> $insert_id,
       'user_id'=> $user_id
    ));
}

Hope this helps and I hope the repo owner chimes in about whether there is a more proper way to use his Model class to insert into a pivot table rather than just using the default db class.

[sethwhitaker]

Based on this issue https://github.com/avenirer/CodeIgniter-MY_Model/issues/87 It looks like inserting into a many to many (pivot) relationship in one step is not possible at the moment, so you will likely need to do what Ive done above.