In CodeIgniter 4, the Join() query builder function allows users to join 2 or multiple tables and manipulate data from database tables.
JOIN query builder clause returns all rows from both table if there are matches in both tables, Otherwise, the result is NULL.
Through this tutorial, you will learn how to join 2,3,4, or multiple tables for fetching/getting data from database tables in codeIgniter 4 using a join query builder with multiple where clauses.
Here are some examples to join 2,3,4 or multiple table:
Example 1: Codeigniter 4 join 2 Tables
The following example code is querying the “cities” table and performing a left join with the “country” table based on a foreign key relationship:
$builder = $db->table('cities');
$builder->select('*');
$builder->join('cities', 'cities.country_id = country.id', 'left');
$query = $builder->get();
Example 2: Codeigniter 4 join 3 Tables
This example code is querying the “cities” table and performing left joins with both the “states” and “countries” tables based on the relationships defined by the foreign key constraints:
$builder = $db->table('cities');
$builder->select('*');
$builder->join('states', 'states.id = cities.state_id', 'left');
$builder->join('countries', 'countries.id = cities.country_id', 'left');
$query = $builder->get();
Example 3: CodeIgniter 4 Join() with Multiple Conditions
In this example, get data using Codeigniter query builder join with multiple where conditions, you can see the following example:
$builder = $db->table('cities');
$builder->select('*');
$builder->join('cities', 'cities.country_id = country.id', 'left');
$builder->join('states', 'states.id = cities.state_id', 'left');
$builder->where('country.status', 'active');
$builder->where('states.status', 'active');
$builder->where('cities.status', 'active');
$query = $builder->get();
Conclusion
That’s it, you have learned how to use codeIgniter query buider join() to join 2, 3, or multiple tables with multiple conditions.
