How to correctly update batch according to post.id

So I currently have three tables.

ci_posts:
id, title, description.

ci_terms:
term_id,title,slug

ci_terms_relationship:
id, post_id, term_id

I have a function in which I can easily add terms to any post, the problem comes when I'm trying to update one of the post. Lets say I created a post with ONE term and I publish it, now I want to update it and put more(more than ONE) than the main term into it. 

PHP Code:

$terms = $this->input->post('categories[]');;

            $data = array();

            foreach ($terms as $term){
                $data[] = array(
                    'post_id'  => $id,
                    'term_id'  => $term, 
                    'type'      =>  'category',
                );
            }

            $this->db->update_batch('ci_terms_relationship', $data, 'post_id'); 


The var $id comes from the id of the ci_posts table. As I said before, it currently updates the relationship with ONE term(as said above; from the add function which is not listed here).

And if I create a new post with at least 5 terms(different term_id) when updating it, it changes the term_id(all of them) to the first term_id of the selected terms.

Thanks in advance.

I do Front-End development most of the time 

You have ID column in your ci_terms_relationship table. Use that id. 

If this does not work for you you can implement your own batch update function. 
Tutorial is here: https://github.com/bcit-ci/CodeIgniter/w...se-Drivers

It would be better if you copy update_batch function from system folder and then change it (don't forgot to rename it) for your needs

It's because you are using post_id as row identifier, but that's not unique per record. You need to use ci_terms_relationship.id if you want to use update_batch.

Didn't even know update_batch was a thing. Had a quick look at CI documentation, and at first glance it seems ok for couple of records, but the size of the SQL query could potentially spiral out of control very quick with number of records or number of fields to update.

And you might not even need it, if you only need to keep simple groups of term IDs for each post ID, and nothing more.

In that case, I'd usually compare two arrays, one for existing terms and one for new updated terms, and generate two new arrays:
- if ID is present in existing and updated terms, I ignore it
- if ID is present in existing but not in updated terms, I add it to delete array
- if ID is not present in existing but is in updated terms, I add it to insert array

Then, if anything is in delete array, I delete it using where_in('id', $delete) method, and if anything is in insert array, I populate necessary additional column data (add post_id, category) and use insert_batch.

It's because you are using post_id as row identifier, but that's not unique per record. You need to use ci_terms_relationship.id if you want to use update_batch.

Didn't even know update_batch was a thing. Had a quick look at CI documentation, and at first glance it seems ok for couple of records, but the size of the SQL query could potentially spiral out of control very quick with number of records or number of fields to update.

And you might not even need it, if you only need to keep simple groups of term IDs for each post ID, and nothing more.

In that case, I'd usually compare two arrays, one for existing terms and one for new updated terms, and generate two new arrays:
- if ID is present in existing and updated terms, I ignore it
- if ID is present in existing but not in updated terms, I add it to delete array
- if ID is not present in existing but is in updated terms, I add it to insert array

Then, if anything is in delete array, I delete it using where_in('id', $delete) method, and if anything is in insert array, I populate necessary additional column data (add post_id, category) and use insert_batch.

That's what I'm trying to do but I just don't find any way of accomplishing it and I have never done something like this before so that's why I'm having troubles. I just created this but it did not work, you can check it if you think you can make it work or something:

Updated controller function(notice that I have to create two foreach with different vars "$data[]" and "$data2[]"):

PHP Code:

           // Create many-to-many relationship          
            
            $terms = $this->input->post('categories[]');;

            $data = array();

            foreach ($terms as $term){
                $data[] = array(
                    'post_id'  => $id,
                    'term_id'  => $term,
                    'type'      =>  'category',
                );
            }

            // Create terms
            $slug = str_replace(' ', '-', $this->input->post('title'));
            $slug = strtolower($slug);

            foreach($terms as $term){
                $data2[] = array(
                    'term_id'   => $term,
// I really have not idea how can I create the title knowing that the input comes from the ci_terms table(terms already created)
                    'title'     => $this->input->post('categories[]'),
                    'slug'      => $slug,
                    'type'      => 'category'
                );
            }

            // Check if terms already exists
            $existing_terms = $this->Terms_model->existing_terms($this->input->post('categories[]'));
            $existing_relationships = $this->Terms_model->existing_relationships($this->input->post('categories[]'));

            if (!empty($existing_terms)) {
                // If terms already exist, update them
                $this->db->update_batch('ci_terms', $data2, 'term_id');
            } elseif(empty($existing_terms)){
                // If they do not exist, create them
                $this->db->insert_batch('ci_terms', $data2);
            } elseif(empty($existing_relationships)){
                // If relationship does not exist between post_id and term_id, create it
                $this->db->insert_batch('ci_relationship', $data);
            } elseif(!empty($existing_relationships)){
                // If relationship already exist between post_id and term_id, update them(in case we take them(terms) out or create more)
                $this->db->insert_batch('ci_relationship', $data, 'post_id');
            } 


and here is the model function to check if they already exists whether on ci_terms or ci_relationships:

PHP Code:

   // If terms already exist, update them
    public function existing_terms($term) {
        $query = $this->db->get_where($this->table, array('term_id' => $term));
        $query = $this->db->get_where($this->table, array('type' => 'category'));
        return $query->array();
    }
    
    // If relationship does not exist, create it
    public function existing_relationships($relationship) {
        $query = $this->db->get_where('ci_relationship', array('term_id' => $relationship));
        $query = $this->db->get_where('ci_relationship', array('type' => 'category'));
        return $query->array();
    } 


NOTE: "$this->table" refers to the ci_terms table

I do Front-End development most of the time 

There are multiple ways to do this, here's one way:

PHP Code:

$postID = 1;

$existingTerms = [1, 2, 3]; // you get this by SELECT term_id FROM ci_terms_relationship WHERE post_id = $postID
$updatedTerms = [1, 3, 4, 5]; // from $this->input->post('categories[]');

$toDelete = array_diff($existingTerms, $updatedTerms); // filter out all term IDs that exist in DB but are not selected for update
print_r($toDelete); // [2]

$toInsert = array_diff($updatedTerms, $existingTerms); // filter out all terms not yet stored in DB, but are selected for update
print_r($toInsert ); // [4, 5]

if ($toDelete) {
    $this->db
        ->where('post_id', $postID)
        ->where_in('term_id', $toDelete)
        ->limit(count($toDelete))
        ->delete('ci_terms_relationship');
}

if ($toInsert) {
    $dbData = [];
    foreach ($toInsert as $termID) {
        $dbData [] = [
            'post_id' => $postID,
            'term_id' => $termID,
            'type' => 'category'
        ];
    }
    $this->db->insert_batch('ci_terms_relationship', $dbData);
} 


This is one reason why you always have a plain ID auto increment column in your tables
to make every record unique.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

This is one reason why you always have a plain ID auto increment column in your tables to make every record unique.

I'm not entirely sure you have to, but that's just my gut feel and not based on any scientific evidence.

Post ID and Term ID already give that unique-ness per row, so not having to deal with that intermediate ID seems to make set up simpler, however I'm open to suggestions where there's clear benefit to db performance or race conditions

Did a quick search on Google but couldn't see anything obvious.

Didn't even know update_batch was a thing. Had a quick look at CI documentation, and at first glance it seems ok for couple of records, but the size of the SQL query could potentially spiral out of control very quick with number of records or number of fields to update.

update_batch has $batch_size parameter,default is 100, if there are more the than 100 records it breaks them for clusters with 100 rows. so I think the number of rows will not affect it.
(My English is not good, so I might misunterstood your point)

update_batch has $batch_size parameter,default is 100, if there are more the than 100 records it breaks them for clusters with 100 rows. so I think the number of rows will not affect it.
(My English is not good, so I might misunterstood your point)

Ah excellent, yeah that was exactly my point, so there's absolutely nothing wrong with your English