Introduction

In this article, we'll explore how to use the JOIN query in CodeIgniter's Active Record. We'll cover different types of joins, provide step-by-step explanations, code snippets, and conclude with a complete code example. Additionally, we'll discuss tips and common pitfalls to help you avoid common mistakes.

✅ Step 1: Database Structure

Tables:

users Table

orders Table

✅ Step 2: CodeIgniter JOIN Query

Inner Join Example

$this->db->select('users.name, orders.product, orders.amount');
$this->db->from('users');
$this->db->join('orders', 'users.id = orders.user_id');
$query = $this->db->get();

$result = $query->result_array();

Explanation

1. select(): Selects the columns to retrieve.

2. from(): Specifies the main table (users).

3. join(): Combines the users table with the orders table using the foreign key user_id.

4. get(): Executes the query and retrieves the result.

✅ Step 3: Types of Joins

Left Join

$this->db->join('orders', 'users.id = orders.user_id', 'left');

Returns all rows from users, with matching rows from orders, and fills with NULLs when no match is found.

Right Join

$this->db->join('orders', 'users.id = orders.user_id', 'right');

Returns all rows from orders, with matching rows from users, and fills with NULLs when no match is found.

✅ Step 4: Complete Code Example

class UserModel extends CI_Model {
    public function get_user_orders() {
        $this->db->select('users.name, orders.product, orders.amount');
        $this->db->from('users');
        $this->db->join('orders', 'users.id = orders.user_id');
        $query = $this->db->get();

        return $query->result_array();
    }
}

Tips

• Always specify the columns in select() to avoid fetching unnecessary data.

• Ensure foreign key relationships are correctly defined in the database.

• Test different join types to understand their behavior with your data.

Common Pitfalls

• Using the wrong join type can lead to missing or duplicated data.

• Not handling NULL values when using outer joins.

• Failing to index the foreign key column can result in performance issues.

✅ Conclusion

Using JOIN queries in CodeIgniter allows you to combine data from multiple tables efficiently. By understanding the different types of joins and following best practices, you can avoid common pitfalls and improve the performance of your queries.