Last updated on February 10, 2018
I am gonna use CodeIgniter’s active record class to generate Join Queries in this tutorial. Because by using AR class we can write complex joins very easily and a major benefit to using the Active Record features is that it allows you to create database independent applications since the query syntax is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system.
Codeigniter JOIN Query
we use join() method to join the 2 or more tables in codeigniter.
Normal Join
$this->db->select('*');
$this->db->from('articles');
$this->db->join('category', 'category.id = articles.id');
$query = $this->db->get();
// Produces:
// SELECT * FROM articles JOIN category ON category.id = articles.id
Join With Condition
$this->db->select('*');
$this->db->from('articles');
$this->db->join('category', 'category.id = articles.id');
$this->db->where(array('category.id' => 10));
$query = $this->db->get();
// Produces:
// SELECT * FROM articles JOIN category ON category.id = articles.id where category.id = 10
Join with multiple tables
You need to call join() method multiple times if you need several joins in one query.
$this->db->select('*');
$this->db->from('articles');
$this->db->join('category', 'category.id = articles.id');
$this->db->join('comments', 'comments.id = articles.id');
$query = $this->db->get();
// Produces:
// SELECT * FROM articles
// JOIN category ON category.id = articles.id
// JOIN comments ON comments.id = articles.id
If you need a specific type of JOIN you can specify it via the third parameter of the function. Options are: left, right, outer, inner, left outer, and right outer.
Join with specific type (left, right, outer, inner, left outer, and right outer)
You need to call join() method multiple times if you need several joins in one query.
$this->db->select('*');
$this->db->from('articles');
$this->db->join('category', 'category.id = articles.id','left');
$this->db->join('comments', 'comments.id = articles.id','left');
$query = $this->db->get();
// Produces:
// SELECT * FROM articles
// LEFT JOIN category ON category.id = articles.id
// LEFT JOIN comments ON comments.id = articles.id
Do share it and let your friend’s know about it.
wpDiscuz