How to use joins in codeIgniter

How to use joins in CodeIgniter?

When working with databases, it's quite common to encounter scenarios where you need to gather information from multiple tables. While raw SQL gives you powerful ways to combine this data through various types of joins, doing this programmatically can often seem like a daunting task. Enter CodeIgniter, a PHP framework that offers simple yet efficient ways to perform these table joins seamlessly. In this blog post, we'll unravel the mysteries of using joins in CodeIgniter, focusing on how you can easily merge data from multiple tables without breaking a sweat.

We all have moments where we want to collect data from more than one table, and joining tables becomes unavoidable. Whether you're looking to find out how a user's comments relate to their profile, or perhaps you want to combine data from several tables for a comprehensive report, CodeIgniter makes this process remarkably straightforward.
 

In many database interactions, one often finds the need to query data from a single table. However, there are instances where information must be retrieved from multiple tables, necessitating the use of join operations. This article aims to elucidate how to effectively implement such operations in CodeIgniter, focusing specifically on the utilization of left and right joins in Codeigniter.

In the ensuing discussion, we will delve into the mechanics of CodeIgniter joins, providing a comprehensive guide on how to amalgamate data from multiple tables in a structured and efficient manner.

This revised introduction retains the essence of your original text while adopting a more formal tone, making it suitable for a professional or academic audience.

In SQL, you always write your query like this SELECT * from users INNER JOIN comments ON users.u_id =  comments.user_id, if there is a match between the columns in both tables users.u_id =  comments.user_id because you are using the INNER JOIN keyword.

This is a Users table

This is a comments table

Now you can write a query in CodeIgniter.
Note: $this->db->select('*') means you are getting all columns from both tables

this->db->select('*');
$this->db->from('users');
$this->db->join('comments','comments.user_id = users.u_id');
$this->db->get();

This chaining (in Codeigniter) means you can chaining multiple methods by using a single statement.

$this->db->select('*')
->from('users')
->join('comments','comments.user_id = users.u_id')
->get();

If you want to use left outer join OR left join, pass the third parameter in join() function/method using Codeigniter.

$this->db->select('*')
->from('users')
->join('comments','comments.user_id = users.u_id','left')//this is the left join in codeigniter
->get();

If you want to use the right outer join in Codeigniter OR right join in Codeigniter, pass the third parameter in join() function/method.

$this->db->select('*')
->from('users')
->join('comments','comments.user_id = users.u_id','right')//this is the right join in codeigniter
->get();

What can you do if you want to join more than two tables? It's simple; I use the third table named a city in this example.

$this->db->select('*')
->from('users')
->join('comments','comments.user_id = users.u_id')
->join('city','city.user_id = users.u_id')
->get();

You can also use the WHERE clause

$this->db->select('*')
->from('users')
->where('users.u_id',1)
->join('comments','comments.user_id = users.u_id')
->join('city','city.user_id = users.u_id')
->get();

joins/multiple joins with a group by in Codeigniter

$this->db->query("select table1.ud_id,table1.ud_date,table1.up_lat_profit_date,table1.ud_currency
                  ,sum(table1.ud_amount*table3.dp_percentage/100) as profit
               from table1
               JOIN `table2` ON `table2`.`deposit_id` = `table1`.`ud_id`
               JOIN `table3` ON `table3`.`dp_id` = `table2`.`daily_profit_id`
               where `table1`.`ud_status` = 1
               AND `table3`.`dp_status` = 1
               AND `table1`.`user_plan_id` = $up_id
               AND table2.pr_status = 1
               group by table1.ud_id");

How can use joins in codeigniter4/CI4

$machineInfoTable =  new ModMachineInfo();//creating the instance
            $data['catData'] = $machineInfoTable->distinct()->
            select('
                           mi_id,mi_slug,mi_name,mi_machine_condition,(select categories.c_name from categories where categories.c_id=machine_info.machine_category) as categoryName,
                           categories.c_name,categories.c_slug
                            ')
                ->where('mi_status',1)
                ->where('machine_category IN (SELECT categories.c_id from categories where categories.c_status = 1 and categories.c_slug="'.$slug.'")')
                ->orderBy('machine_info.mi_id','desc')
                ->join('categories','categories.c_id=machine_info.machine_category')->findAll();

Conclusion

We always use them to fetch the data from multiple tables; managing joins in SQL is difficult, but working in Codeigniter, it's straightforward to use the joins too. It's a left join or a right join in Codeigniter.
We just need to call a method in Codeigniter $this->db->join('table'.'condition','left,right');. Joins take three parameters first is the table, the second is the condition, and the third is the left join or the right join in Codeigniter.