Performing inner and outer SQL joins in CodeIgniter 4

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 


Author, Copyright and citation

Author

Sylwester Wojnowski

Author of the this article - Sylwester Wojnowski - is a sWWW web developer. He has been writing computer code for the websites and web applications since 1998.

Copyrights

©Copyright, 2022 Sylwester Wojnowski. This article may not be reproduced or published as a whole or in parts without permission from the author. If you share it, please give author credit and do not remove embedded links.

Computer code, if present in the article, is excluded from the above and licensed under GPLv3.

Citation

Cite this article as:

Wojnowski, Sylwester. "Performing inner and outer SQL joins in CodeIgniter 4." From sWWW - Code For The Web . https://wojnowski.net.pl//main/index/performing-inner-and-outer-sql-joins-in-codeigniter-4

Add Comment

Allowed BB Code - style tags: [b][/b], [i][/i], [code=text][/code],[code=javascript][/code],[code=php][/code],[code=bash][/code],[code=css][/code],[code=html][/code]


I constent to processing my data given through this form for purposes of a reply by the administrator of this website.

Recent Comments

Nobody has commented on this post yet. Be first!