Join three table codeigniter 4

Question

Hi everyone i'am new on codeigniter 4 and currently working on a small project in the project i'am trying to join three tables and display there data in single table.

Table_1
id unique_id Name
1  1111      Sam   
2  2222      Charlote

Table_2
id unique_id Name
1  1212      Jhon 
2  5151      Alex

Table_3
id author title
1  1111   Book_1
2  5151   Book_2
3  1111   Book_3


Result
------------------------
| No | Author | Title  |
------------------------
| 1  | Sam    | Book_1 |
| 2  | Alex   | Book_2 |
| 3  | Sam    | Book_3 |
------------------------

I've tried to join with, but not working.

        $this->join('Table_1', 'Table_1.unique_id = Table_3.author ');
        $this->join('Table_2', 'Table_2.unique_id = Table_3.author ');
        $this->select('Table_1.Name');
        $this->select('Table_2.Name');
        $this->select('Table_3.*');
        $this->orderBy('Table_3.id');
        return  $this->findAll();

Is there another way to Join them? Thank you

Answer 1

What you currently have won't work because your Table_1 and Table_2 are effectively the same table.

Taking your Attempt and correcting it to use LEFT JOIN

public function example_1() {
    $this->join('Table_1', 'Table_1.unique_id = Table_3.author', 'LEFT');
    $this->join('Table_2', 'Table_2.unique_id = Table_3.author', 'LEFT');
    $this->select('Table_1.Name');
    $this->select('Table_2.Name');
    $this->select('Table_3.*');
    $this->orderBy('Table_3.id');
    $result = $this->findAll();

    echo $this->db->getLastQuery();

    return $result;
}

You would get...

SELECT `Table_1`.`Name`, `Table_2`.`Name`, `Table_3`.*
FROM `Table_3`
LEFT JOIN `Table_1` ON `Table_1`.`unique_id` = `Table_3`.`author`
LEFT JOIN `Table_2` ON `Table_2`.`unique_id` = `Table_3`.`author`
ORDER BY `Table_3`.`id`

array(3) {
  [0]=>
  array(4) {
    ["Name"]=>
    NULL
    ["id"]=>
    string(1) "1"
    ["author"]=>
    string(4) "1111"
    ["title"]=>
    string(6) "Book_1"
  }
  [1]=>
  array(4) {
    ["Name"]=>
    string(4) "Alex"
    ["id"]=>
    string(1) "2"
    ["author"]=>
    string(4) "5151"
    ["title"]=>
    string(6) "Book_2"
  }
  [2]=>
  array(4) {
    ["Name"]=>
    NULL
    ["id"]=>
    string(1) "3"
    ["author"]=>
    string(4) "1111"
    ["title"]=>
    string(6) "Book_3"
  }
}

Note that you have, two occurrences of name in your query. So which one will win? It appears that Table_2.name is only used and any reference to Table_1.name is NULL as it's not used.

You could give them different names using aliases but then you would have something like name_1 and name_2 so which one is it? This is due to the duplication in your Table_1 and Table_2 and you asking for both.

The Better way So in this case you would need to perform an UNION on Table_1 and Table_2.

I don't think that there is a UNION command in the CI query builder.

Using mysql, it would be...

public function get_book_and_author() {
    $sql = "SELECT Table_3.id, T12.name as author, Table_3.title 
            FROM (
                SELECT Table_1.* FROM Table_1
            UNION
                SELECT Table_2.* FROM Table_2
                ) as T12
            LEFT JOIN Table_3 ON T12.unique_id = Table_3.author  
            WHERE Table_3.author IS NOT NULL
            ";
    $result = $this->db->query($sql);

    return $result->getResultArray();
}

So in this example, we have specified the 3 fields you require in the Select. Note the T12.name is renamed author. (See the output below)

Then an UNION has to be performed on Table_1 and Table_2 and the result is named (aliased) as T12 (shorthand for Table_1 and Table_2) as the result requires a new name.

Then a LEFT JOIN is performed against Table_3, which will give all combinations where there will be NULLS, so the WHERE statement filters those out using "IS NOT NULL" on Table_3.author.

I left out the ORDER BY as it's not really needed and you can add that back in if you wish to.

A var_dump() of the result gives...

array(3) {
  [0]=>
  array(3) {
    ["id"]=>
    string(1) "1"
    ["author"]=>
    string(3) "Sam"
    ["title"]=>
    string(6) "Book_1"
  }
  [1]=>
  array(3) {
    ["id"]=>
    string(1) "2"
    ["author"]=>
    string(4) "Alex"
    ["title"]=>
    string(6) "Book_2"
  }
  [2]=>
  array(3) {
    ["id"]=>
    string(1) "3"
    ["author"]=>
    string(3) "Sam"
    ["title"]=>
    string(6) "Book_3"
  }
}

So that will give you the id,author and title for each matching row as you have requested using your example Tables.