Performing inner and outer SQL joins in CodeIgniter 4

Created:04 Feb 2022 22:19:19 , in  Web development

In this post I look at how to perform inner and outer SQL joins using CodeIgniter 4 prepared queries and Query Builder class. I assume you know what SQL joins are and when to use them in MySQL. This article describes a specific situation. There are three tables, posts, galleries and posts_galleries. A post can have many galleries and a gallery can be attached to many posts (a many-to-many relationship). All relationships between particular posts and particular galleries are stored in posts_galleries table.

Database table setup

Each post stored in posts table, each gallery stored in galleries table and each item stored in posts_galleries table has an unique id. Posts_galleries table is what stores relationships between post and galleries using columns called postId and galleryId.

Table posts:

+------------------+--------------------------------------+------+-----+---------+----------------+
| Field            | Type                                 | Null | Key | Default | Extra          |
+------------------+--------------------------------------+------+-----+---------+----------------+
| id               | int(5) unsigned                      | NO   | PRI | NULL    | auto_increment |
| title            | varchar(200)                         | NO   | MUL | NULL    |                |
| content          | longtext                             | NO   |     | NULL    |                |
+------------------+--------------------------------------+------+-----+---------+----------------+

Table galleries:

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(5) unsigned  | NO   | PRI | NULL    | auto_increment |
| title       | varchar(200)     | NO   |     | NULL    |                |
| description | text             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

Table posts_galleries:

 
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | int(5) unsigned     | NO   | PRI | NULL    | auto_increment |
| postId    | bigint(20) unsigned | NO   | MUL | NULL    |                |
| galleryId | int(5) unsigned     | NO   | MUL | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+ 

Making join queries in CodeIgniter 4

To find all galleries (and select their id and title), which are attached to a post using a prepared query in CodeIgniter 4:

public function getAttachedGalleries(){
  $sql = "SELECT galleries.`id`,galleries.`title` FROM galleries INNER JOIN posts_galleries ON galleries.`id` = posts_galleries.`galleryId`;";

  return $this -> db -> query($sql) -> getResult();
}

and to do the same using CodeIgniter 4 Query Builder Class:

public function getAttachedGalleries(){
  $builder = $this -> db -> table('galleries g'); 
  $query = $builder -> select(['g.id','g.title']) -> from(['posts_galleries pg']) -> join('posts_galleries','g.id = pg.galleryId','inner');
     
  return $builder -> get() -> getResult();
}

To find all the galleries (and select their id and title), which are or are not attached to a post using prepared queries in CodeIgniter 4:

public function getAttachedAndDeatchedGalleries(){
  $sql = "SELECT galleries.`id`,galleries.`title` FROM galleries LEFT OUTER JOIN posts_galleries ON galleries.`id` = posts_galleries.`galleryId`;";

  return $this -> db -> query($sql) -> getResult();
}

To do the same using CodeIgniter 4 Query Builder Class:

public function getAttachedAndDeatchedGalleries(){
  $builder = $this -> db -> table('galleries g'); 
  $query = $builder -> select(['g.id','g.title']) -> from(['posts_galleries pg']) -> join('posts_galleries','g.id = pg.galleryId','left outer');
     
  return $builder -> get() -> getResult();
}

The above code are a model methods and should be called in a controller.

A couple of extras

For query builder queries you should make sure database tables are aliased or you will get MySQLi error #1066 (assuming you are using MySQL database).

Also, it is often not immediately obvious what Query Builder class does with your SQL under the hood. If you want to see the query the class compiled before it is run:

echo $query -> getCompiledSelect();

It is worth noting, that similar methods are available for update insert and delete type SQL queries: getCompiledUpdate(), getCompiledInsert(), getCompiledDelete()).

Where to look for details and further info

If you need more information on how to perform join and other queries in CodeIgniter 4, you might want to take a close look at Working With Databases chapter of documentation for the framework. Have fun!

This post was updated on 04 Feb 2022 22:20:22

Tags:  CodeIgniter ,  mysql ,  php