Kohana 3.2 ORM questions

Question

Let's summarize what i want to ask.

I have a category table and a news table.

So a category can have many news as well as sub categories. If i delete a category, the code need to find the category's sub categories as well as news related to that category and it's sub categories to delete too. Currently my code looks like so(It works well at the moment):

The relationship:

public $_has_many = array(
        'news' => array(
            'model'         =>  'news',
            'foreign_key'   =>  'cat_id'
        )
    );   

The code to delete:

/*
     *  @param $ids: The array of category id that we want to delete
     */
    public function delete_items($ids){

        if(!is_array($ids))
            $ids = array($ids);

        if(is_array($ids)){
            $recursive = new System_Recursive();
            /*
             *  list_items() method will simply return all records for the category table
             */
            $source = $this->list_items(null);

            /*
             *  Loop through the category ids
             */
            foreach($ids as $id){

                $result = $this->where('id', '=', $id)->find();
                if($result->loaded()){

                    // If category found, then find all the news related to that category
                    $main_category_news = $result->news->find_all();
                    if($main_category_news){
                        // Loop through all the news and proccess the delete method
                        foreach($main_category_news as $main_news){
                            $main_news->delete();
                        }
                    }

                    /*
                     *  The find_children() method returns all sub categories of the current category ($result)
                     */
                    $recursive->find_children($source, $result->id, $arr_children, false);

                    if($arr_children){
                        // If any sub categories found, continue to loop :((, terrible
                        foreach($arr_children as $child){
                            $this->clear();
                            $child_result = $this->where('id', '=', $child)->find();
                            if($child_result->loaded()){
                                /*
                                 * Again, find news related to this sub category and then loop through the news to do single delete
                                 */
                                $child_news = $child_result->news->find_all();
                                foreach($child_news as $c){
                                    $c->delete();
                                }
                            }
                            /*
                             *  After deleting news for sub category, 
                             *  I use clear to prevent error from loaded object
                             *  Then find "again" the sub category to delete it
                             */
                            $this->clear();
                            $child_delete = $this->where('id','=',$child)->find();
                            if($child_delete->loaded()){
                                $child_delete->delete();
                            }
                        }
                    }

                    /*
                     *  And finally for the main direct category 
                     */
                    $this->clear();
                    $result = $this->where('id', '=', $id)->find();
                    $result->delete();
                }
            }
        }

There are so many many loops in the code, let's consider we delete about 5 categories in 50 categories all with 500 news related to each category. I don't know but i think this is going to take a whole day to complete the task.

So, can someone give me a hint of completing this code for the right way: how to make less code? How to reduce the loops? Is it possible to create a function to reuse in this case, for example if news has many tags, we would do the same thing here and just call that method and it's good to go?

Please help me with this. If you don't answer it, please also give me a reason for why you don't, so that i can make more meaningful questions.

Thank you

Answer 1

It could be faster if you write plain SQL query in your Model function, something like this

$query = DB::query(Database::DELETE, 'DELETE FROM news WHERE news.id IN
(
SELECT news.id FROM news
JOIN sub_category ON news.sub_category_id = sub_category.id
JOIN category ON sub_category.category_id = category.id
WHERE category.id = '.$this->id.'
)'
);

I'm not sure of your exact DB design, so you may need to alter SQL code, but you get main idea.

Answer 2

Let's assume you are using InnoDB (what is likely). You would not need any PHP code thanks to the foreign key constraints.

There are alot of tutorials out there, the solution is using

ON UPDATE CASCADE
ON DELETE CASCADE

when creating the parent's foreign key. When you update a parent all foreign keys will be updated as well (if necessary, so usually when the id changes), also if you delete a parent all children will be deleted as well.