**Implementing Models and Relationships for a Blog System with Optimized Queries** In this lab topic, we will explore the implementation of models and relationships for a blog system in CodeIgniter, with a focus on optimized queries to improve performance. **What You Will Learn** * Understanding the concept of models in CodeIgniter and their role in database interactions * Creating and configuring models for a blog system * Implementing relationships between tables using one-to-one, one-to-many, and many-to-many relationships * Optimizing database queries using eager loading and joins * Using CodeIgniter's Query Builder and Active Record to interact with the database **Prerequisites** * Completion of the previous topics on CodeIgniter fundamentals and database integration **Step 1: Creating the Blog Model** Create a new file called `Blog_model.php` in the CodeIgniter `app/includes/models` directory. ```php <?php class Blog_model extends CI_Model { private $_table = 'blogs'; public function __construct() { parent::__construct(); $this->load->database(); } public function get_blogs() { return $this->db->get($this->_table)->result(); } public function get_blog($id) { return $this->db->get_where($this->_table, array('id' => $id))->row(); } public function create_blog($data) { return $this->db->insert($this->_table, $data); } public function update_blog($id, $data) { return $this->db->where('id', $id)->update($this->_table, $data); } public function delete_blog($id) { return $this->db->where('id', $id)->delete($this->_table); } } ``` This implementation defines a `Blog_model` class that interacts with the `blogs` table in the database. The class includes methods for retrieving, creating, updating, and deleting blog posts. **Step 2: Defining Relationships** In this example, we will create relationships between the `blogs` table and other tables. We will use one-to-one, one-to-many, and many-to-many relationships. 1. **One-to-one relationship between Blog and Categories** Create a new file called `Blog_category_model.php` in the `app/includes/models` directory: ```php <?php class Blog_category_model extends CI_Model { private $_table = 'categories'; public function __construct() { parent::__construct(); $this->load->database(); } public function get_categories() { return $this->db->get($this->_table)->result(); } public function get_category($id) { return $this->db->get_where($this->_table, array('id' => $id))->row(); } public function update_category($id, $data) { return $this->db->where('id', $id)->update($this->_table, $data); } public function create_category($data) { return $this->db->insert($this->_table, $data); } } ``` The `Blog_category_model` class interacts with the `categories` table and defines a one-to-one relationship with the `blogs` table. 2. **One-to-many relationship between Blogs and Comments** Create a new file called `Comment_model.php` in the `app/includes/models` directory: ```php <?php class Comment_model extends CI_Model { private $_table = 'comments'; public function __construct() { parent::__construct(); $this->load->database(); } public function get_comments($blog_id) { return $this->db->get_where($this->_table, array('blog_id' => $blog_id))->result(); } public function create_comment($data) { return $this->db->insert($this->_table, $data); } public function update_comment($id, $data) { return $this->db->where('id', $id)->update($this->_table, $data); } public function delete_comment($id) { return $this->db->where('id', $id)->delete($this->_table); } } ``` The `Comment_model` class interacts with the `comments` table and defines a one-to-many relationship with the `blogs` table. **Step 3: Many-to-many relationship between Categories and Tags** Create a new file called `Tag_model.php` in the `app/includes/models` directory: ```php <?php class Tag_model extends CI_Model { private $_table = 'tags'; public function __construct() { parent::__construct(); $this->load->database(); } public function get_tags($category_id) { return $this->db->get_where($this->_table, array('category_id' => $category_id))->result(); } public function get_tag($id) { return $this->db->get_where($this->_table, array('id' => $id))->row(); } public function create_tag($data) { return $this->db->insert($this->_table, $data); } public function update_tag($id, $data) { return $this->db->where('id', $id)->update($this->_table, $data); } } ``` The `Tag_model` class interacts with the `tags` table and defines a many-to-many relationship with the `categories` table. **Step 4: Optimizing Queries** To optimize queries, we will use eager loading and joins. Create a new file called `Blog_relationships.php` in the `app/coalited/models` directory: ```php <?php class Blog_relationships extends CI_Model { public function get_related_data() { $this->db->select('blogs.*, categories.name as category_name, comments.id as comment_id, comments comment_text'); $this->db->from('blogs'); $this->db->join('blog_categories', 'blogs.id = blog_categories.blog_id'); $this->db->join('tags', 'blog_categories.id = tags.category_id'); $this->db->join('comments', 'tags.id = comments.tag_id'); return $this->db->get()->result(); } } ``` The `Blog_relationships` class defines a single query that retrieves related data from multiple tables. Try this out and verify that the relationships are working as expected. **Conclusion** In this lab topic, we have implemented models and relationships for a blog system in CodeIgniter, with a focus on optimized queries to improve performance. We have used one-to-one, one-to-many, and many-to-many relationships, and applied different techniques to optimize queries. This should give you a solid foundation for working with models and relationships in CodeIgniter. **Assessment** Congratulations! You have now completed the lab topic "Implementing models and relationships for a blog system with optimized queries". Please submit your code to the instructor for review. **Questions and Comments** Is there anything you're still having trouble with or any questions about the topic?