Inserting data with has_many

zwelldesigns · June 2020

Hi,

I'm wondering where I can look to find information about using queries to insert data for a simple module and then populating the fuel_relationships table based on this data. In my simple module, the user can upload data for the Dictionary in bulk. If entries exist, the existing entry is updated. If entries don't exist, they're inserted. I handle this in my Dictionary model. The theme for each entry is has_many. The code in the model is below (note that this code is not functional because I've been messing around with it -- it was functional when I did not do the on_after_save hook to try and insert data into the fuel_relationships table):

public function d_upload($data = array()){ 
        $inserts = 0; 
        $updates = 0; 

        foreach($data as $k=>$d){ 
            $this->db->where('slug', $d['slug']);
            $query = $this->db->get('slp_dictionary');
            if ($query->num_rows() > 0){
                if(!empty($d['examples'])){ 
                    $d['examples'] = json_encode($d['examples']); 
                }

                $d = $this->on_before_save($d);
                $this->db->where('slug', $d['slug']);
                $this->db->set($d); 
                $this->db->update('slp_dictionary');
                $this->on_after_save(array('update', 'candidate_id'=>$query->result_array(), 'foreign_id'=>$d['theme']));
                $updates = $updates+1; 
            }
            else{
                if(!empty($d['examples'])){ 
                    $d['examples'] = json_encode($d['examples']); 
                }
                $d = $this->on_before_save($d);
                $this->db->set($d); 
                $this->db->insert('slp_dictionary'); 
                $inserts = $inserts+1; 
            }
        } 
        return $inserts.' new entries created and '.$updates.' existing entries updated successfully.';

public function on_before_save($values)
    {
        // this inserts the user ID of the person who edited this last 
        $CI=get_instance(); 
        $this->_editable_by_user();
        $values['last_modified_by'] = (!empty($this->limit_to_user_field) AND !empty($values['last_modified_by'])) ? $values['last_modified_by'] : $CI->fuel->auth->user_data('id');
        $values['publish_date'] = datetime_now();
        //$values['theme'] = implode($values['theme'], ','); 
        return $values;
    }
    public function on_after_save($values)
    {
        switch($values[0]){ 
            case "update": 
                $this->db->where(array('candidate_table'=>'slp_dictionary', 'candidate_key'=>$values['candidate_id'][0]['id']));
                $updateQ = $this->db->get('fuel_relationships'); 
                if(is_array($values['foreign_id'])){ 
                    if($updateQ->num_rows() < count($values['foreign_id'])){ 
                        echo 'less'; 
                    }
                    else{ 
                        echo 'more';
                    } 
                    foreach($values['foreign_id'] as $id){
                        $this->db->where(array('candidate_table'=>'slp_dictionary', 'candidate_key'=>$values['candidate_id'][0]['id']));
                        $this->db->set(array('foreign_key'=>$id));
                    }
                }
                else{ 
                    $this->db->set(array('foreign_key'=>$values['foreign_id']));
                } 
                //$this->db->update('fuel_relationships'); 
            break; 
            default: 

            break; 
        }   
    }

I couldn't find any info on using has_many with insert() and update() and I couldn't find any samples in source code, so I was winging it so that the on_after_save hook would manipulate the fuel_relationships table. I got it to work in simple cases where a single existing entry has a single foreign key, but now I'm lost in the rabbit hole when an existing entry has one existing key, but not another and I need to update one and create another. (Logically, I know how to do it, but it seems like overkill and a time suck.)

So I'm wondering if there's a quick and easy way of inserting has_many foreign keys/updating existing ones?

Thanks!

zwelldesigns · June 2020

PS I also tried process_related(), but I got an error that the method didn't exist because I didn't extend My_Model.

admin · June 2020

I'm not quite sure I follow. Could you explain what the data looks like that is being sent in the $values array to theon_before_save() and on_after_save() functions?

Also, a few notes:
1. the on_after_save() method calls a function called process_relationships($id) so it's important to call parent::on_after_save($values) when overriding that method.
2. The on_after_save() method should be thought of as "what to do after saving a single record of data" so it's important to have that $values array to be in a format that would make sense when processing a single record.
3. The model's save($values) function will detect whether the $values passed to the save() method is a numerically indexed array of data, in which case it will loop through it and save for each item in the array, or a single record's data.
4. The model's save() function will do the insert/update detection for you.

zwelldesigns · July 2020

I think that my problem might be originating from my database structure, but I could not find anything in the has_many docs that tells me what might be wrong. Right now, I have two tables/models:

slp_dictionary (model: Dictionary_model) = dictionary entries (has_many = Themes_model)
slp_themes (model: Themes_model) = the themes that dictionary entries are categorized under

Basically my idea is that entries can be listed under multiple themes. But the reason why I think my database architecture is problematic is that when I edit individual dictionary entries, the has_many list of themes is generated correctly, but the saved values in the right-hand column disappear upon saving. The fuel_relationships table DOES get updated, but the chosen themes do not appear in the right-hand column of the has_many select box. I'm hoping if I figure this issue out, then I will be able to figure out my other issues.

The structure of these two tables is as follows:

slp_dictionary
# Name Type Collation Attributes Null Default Comments Extra
1 id Primary int(11) No None AUTO_INCREMENT
2 headword Index varchar(255) utf8_general_ci No None
3 headword_gur varchar(255) utf8_general_ci No None
4 slug varchar(255) utf8_general_ci No None
5 gloss_en varchar(255) utf8_general_ci No None
6 gloss_nep varchar(255) utf8_general_ci No None
7 audio longtext utf8_general_ci Yes NULL
8 examples longtext utf8_general_ci Yes NULL
9 img longtext utf8_general_ci Yes NULL
10 note longtext utf8_general_ci Yes NULL
11 publish_date datetime Yes NULL
12 last_modified timestamp on update CURRENT_TIMESTAMP Yes CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
13 last_modified_by int(10) UNSIGNED No None
14 precedence int(11) No None
15 published enum('yes', 'no') ascii_general_ci No yes

slp_themes
# Name Type Collation Attributes Null Default Comments Extra
1 id Primary int(11) No None AUTO_INCREMENT
2 slug varchar(255) ascii_general_ci No None
3 theme_rom varchar(255) utf8_unicode_ci No None
4 theme_gvr varchar(255) utf8_unicode_ci No None
5 theme_en varchar(255) utf8_unicode_ci No None
6 theme_nep varchar(255) utf8_unicode_ci No None
7 img varchar(255) utf8_unicode_ci No None
8 precedence int(11) No None
9 published enum('yes', 'no') ascii_general_ci No yes

In my fuel_relationships table, the candidate_table is slp_dictionary, candidate_key is the ID of the dictionary entry, foreign_table is slp_themes, and foreign_key is the ID of the theme.

Do you see any issues that might interfere with the functionality of has_many here? Also, when you declare has_many, must you ALSO declare belongs_to in the model that the has_many refers to?

Thanks in advance.

zwelldesigns · July 2020

OK, I figured everything out and there was no problem with the database structure, but I still cannot fix the has_many multiselect menu. When I move items from the left to the right, it saves the values in the relationships table, but the right side of the multiselect menu does not show the saved values. Any hints?

admin · July 2020

You many need to set the multi-selects $values manually in the model's form_fields():

public function form_fields($values, $related = array())
{
     $fields = parent::form_fields($values, $related);
     $fields['my_field_name']['value'] = //... index array of values pulled from relationship table
     return $fields;
}

zwelldesigns · August 2020

That did it - thank you!

Howdy, Stranger!

Categories

Inserting data with has_many

Comments