In SQL, JOINs are an operation used to combine two or more relational tables based on a common column or set of columns, generally FK type columns or foreign keys are used to make this connection between different tables. They are used to retrieve data from multiple related tables, allowing the data to be presented in a more complete and meaningful way; for example, if we have a table of posts and another of categories, we can indicate that a post contains a category by means of a FK in the table of posts and then do a JOIN that allows us to connect the table of posts with the table of categories and obtain the full detail of the post; We can do the same with multiple tables.
There are different types of JOINs in SQL, such as INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN, each with a different purpose depending on the user's need.
In the CodeIgniter 4 application, according to the documentation, we have 3 types of joins available. Here are the following:
- Inner Join
- Left Join
- Right Join
- Inner Join: This join fetches data based on a common value condition between two or more than two tables. Based on the matching condition, it will return all the data we expected. Removes those rows from the result set that do not have a matching condition.
- Left Join – This also works the same as the match condition between two or more than two tables. But in this case we also get the rows from the table on the left that do not match the condition of the table on the right side. It means that we get all the rows in the table on the left, including the values in the table on the right.
- Right Join – Rows with the same matching conditions, including all rows on the right side with no match conditions.
The use of joins in CodeIgniter 4 is very easy and all we have to do is build the main query:
$query = $productModel->asObject()->select("pc.*, u.email, puc.description, puc.direction")In this case, an example model, called "productModel" and now, we are going to perform the join(s):
$query = $productModel->asObject()->select("pc.*, u.email, puc.description, puc.direction")
->join('products_control as pc', 'pc.product_id = products.id')In which, you can place as many joins as you need and/or your relationships allow, in my case, a product, has a foreign relationship with "'products_control" and "'products_control" a foreign relationship (fk) with "users" and finally with "'products_users_control"
$query = $productModel->asObject()->select("pc.*, u.email, puc.description, puc.direction")
->join('products_control as pc', 'pc.product_id = products.id')
->join('users as u', 'pc.user_id = u.id')
->join('products_users_control as puc', 'pc.id = puc.product_control_id');Key points
The join function receives two mandatory parameters and a third optional one.
As the first parameter we indicate the name of the table which we want to join.
Optionally, we indicate an alias by "as" to avoid repeating the table name when we want to reference the table: products_control as pc
As a second parameter we indicate the condition; which is generally to place the equality of our relation (fk)
As the third parameter you can specify the type of join: left, right, outer, inner, left outer, and right outer: join('products_control as pc', 'pc.product_id = products.id','left')
Without more to say, the relations that we use in my complete course on CodeIgniter 4:
class ProductModel extends Model
{
protected $table = 'products';
protected $primaryKey = 'id';
protected $allowedFields = ['name', 'code', 'description', 'entry', 'exit', 'stock', 'price'];
public function get($id = null)
{
if ($id === null) {
return $this->findAll();
}
return $this->asArray()
->where(['id' => $id])
->first();
}
}
class ProductControlModel extends Model
{
protected $table = 'products_control';
protected $primaryKey = 'id';
protected $allowedFields = ['product_id', 'type','count', 'created_at', 'updated_at','user_id'];
protected $useTimestamps = true;
protected $createdField = 'created_at';
protected $updatedField = 'updated_at';
public function get($id = null)
{
if ($id === null) {
return $this->findAll();
}
return $this->asArray()
->where(['id' => $id])
->first();
}
}
class ProductUserControlModel extends Model
{
protected $table = 'products_users_control';
protected $primaryKey = 'id';
protected $allowedFields = ['product_id', 'user_id', 'product_control_id', 'type', 'count', 'created_at', 'updated_at', 'description', 'direction'];
protected $useTimestamps = true;
protected $createdField = 'created_at';
protected $updatedField = 'updated_at';
public function get($id = null)
{
if ($id === null) {
return $this->findAll();
}
return $this->asArray()
->where(['id' => $id])
->first();
}
}
<?php
class User extends BaseController{
use ResponseTrait;
}Remember that in the complete CodeIgniter 4 course, you have much more information and data about the use of this framework.
The JOINS, we always use them to obtain the data of several tables; managing joins in SQL is hard, but working in Codeigniter is also easy to use. It is either 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, second is the condition, and third is the left join or right join in Codeigniter.
Develop with Laravel, Django, Flask, CodeIgniter, HTML5, CSS3, MySQL, JavaScript, Vue, Android, iOS, Flutter