Join Method

The join method is used to combine query results from two or more tables with only 1 key is to be joined as condition. This method will execute the JOIN SQL command as well as part of the Query Builder.

Basic Usage

Here is the basic usage join method from $db property on SENE_Model class.

–

$this->db->join(
  string $table2,
  string $table2_alias,
  string $table2_column_to_joined,
  string $table1_alias,
  string $table1_column_to_joined
  [, string $join_method = ""]
): $this->db

Parameters

This method has 5 required parameters and 1 optional parameter.

$table2

The $table2 refers to the name of the table that will be joined to.

$table2_alias

The $table2_alias value can contain about string that aliased the value of $table2.

$table2_column_to_joined

The $table2_column_to_joined value can contain about column name from $table2 to be the key.

$table1_alias

The $table1_alias value can contain about string that aliased the value of origin table.

$table1_column_to_joined

The $table1_column_to_joined value can contain about column name from origin table to be the key.

$join_method

The $join_method purpose is to declare join method that used. Default value is the empty string "". Or can be on of these value:

left
right
inner
outer

Example

The following is an example of using the join method in the d_order_model.php file.

–

<?php
class D_Order_Model extends SENE_Model{
  var $tbl = 'd_order';
  var $tbl_as = 'dor';
  var $tbl2 = 'd_order_detail';
  var $tbl2_as = 'dod';
  var $tbl3 = 'c_produk';
  var $tbl3_as = 'cp';

  public function __construct(){
    parent::__construct();
    $this->db->from($this->tbl,$this->tbl_as);
  }
  public function getByOrderId($d_order_id){
    $this->db->from($this->tbl,$this->tbl_as);
    $this->db->join($this->tbl2, $this->tbl2_as, 'id', $this->tbl_as, 'd_order_id', 'left');
    $this->db->where_as("$this->tbl_as.d_order_id", $this->db->esc($d_order_id));
    return $this->db->get();
  }
  public function getDetailJasaForDrDashboard(){
    $this->db->from($this->tbl, $this->tbl_as);
    $this->db->join($this->tbl2, $this->tbl2_as, 'id', $this->tbl_as, 'd_order_id', '');
    $this->db->join($this->tbl3, $this->tbl3_as, 'id', $this->tbl_as, 'c_produk_id', 'left');
    $this->db->where_as("$this->tbl2_as.utype",$this->db->esc('order_selesai'));
    $this->db->where_as("$this->tbl3_as.jenis_paket", 'IS NULL');
    $this->db->where_as("$this->tbl_as.sdate", 'IS NOT NULL');
    return $this->db->get('',0);
  }
}

SQL Generated Result

The following is the SQL command generated by the method in the D_Order_Model class example.

–

-- result from executing D_Order_Model::getByOrderId(47) --
SELECT *
FROM `d_order` dor
LEFT JOIN d_order_detail dod
  ON dor.id = dod.d_order_id
WHERE dod.`id` = 47;

-- result from executing D_Order_Model::getDetailJasaForDrDashboard() --
SELECT *
FROM `d_order` dor
JOIN d_order_detail dod
  ON dor.id = dod.d_order_id
LEFT JOIN c_produk cp
  ON cp.id = dod.c_produk_id
WHERE
  dod.`utype` = "order_selesai"
  AND cp.utype IS NULL
  AND dor.sdate IS NOT NULL;