codeigniter 4 mysql query builder get subdata from itself

Question

[UPDATED]

the error is now gone but the data result from the codeigniter 4 query builder all has the same id value, unlike the one in phpmyadmin. and if I use the $builder method it now gives the expected result, but I'm still hoping to be able to fully use the query builder properly if possible..

---

I have a mysql table like this

id | nama | hari
-----------------
1  | AAA  | 1
2  | AAA  | 2
3  | AAA  | 3
4  | AAA  | 4
5  | AAA  | 5
6  | BBB  | 1
7  | BBB  | 2
8  | BBB  | 3

when I get a parameter id 1, I want to get the row number 1-5 only. it works when I use this sql in phpmyadmin

select m.* 
from m_rute m
join (
    select m1.*
    from m_rute m1
    where m1.id = 1
) mt on mt.nama_rute = m.nama_rute

then I try to "translate" it using codeigniter 4's query builder but when I test it on postman, it gives a set of data where all the id field is the same, unlike the one in phpmyadmin result.

controller

public function show($id = null)
{
    $db = \Config\Database::connect();
    $builder = $db->table('m_rute as m');
    $builder->select('m.*')->where('m.id', $id);
    $subquery = $builder->getCompiledSelect();

    // this works but wouldn't a fully proper query builder seem nicer?
    //////////////
    // $builder->select('m.*')
    //     ->join('('.$subquery.') as m1', 'm1.nama_rute = m.nama_rute')
    //     ;
    // $q = $builder->get();
    // $data = $q->getResultArray();

    $model = new MRuteModel();

    $model->join('('.$subquery.') as t', 't.nama_rute = m_rute.nama_rute');

    $data  = $model->findAll();

    if (!$data) {
        return $this->failNotFound('Data not found');
    }

    return $this->respond($data);
}

any help is appreciated

Answer 1

Solution 1: Using Raw/Regular Queries.

The query() function returns a database result object when “read” type queries are run which you can use to show your results. When “write” type queries are run it simply returns true or false depending on success or failure.

<?php

namespace App\Models;

use CodeIgniter\Model;

class MRuteModel extends Model
{
    public function findById(int $id)
    {
        $id = $this->db->escape($id);

        return $this->db->query("
            select m.* 
            from m_rute m
                join (
                    select m1.*
                    from m_rute m1
                    where m1.id = {$id}
                ) mt on mt.nama_rute = m.nama_rute")->getResultArray();
    }
}

Solution 2A: Using SQL Joins With Raw Queries.

join($table, $cond[, $type = ''[, $escape = null]])
   Parameters:

      $table (string) – Table name to join

      $cond (string) – The JOIN ON condition

      $type (string) – The JOIN type

      $escape (bool) – Whether to escape values and identifiers

   Returns: BaseBuilder instance (method chaining)

   Return type: BaseBuilder

Adds a JOIN clause to a query.

<?php

namespace App\Models;

use CodeIgniter\Model;

class MRuteModel extends Model
{
    public function findById(int $id)
    {
        $id = $this->db->escape($id);

        return $this->db->table('m_rute m')
            ->select("m.*")
            ->join("
            (select m1.*
                from m_rute m1
                    where m1.id = {$id}
            ) mt", 'mt.nama_rute = m.nama_rute')
            ->get()->getResultArray();
    }
}

Solution 2B: Using SQL Joins With A Query Builder.

getCompiledSelect()

Compiles a SELECT statement and returns it as a string.

<?php

namespace App\Models;

use CodeIgniter\Model;

class MRuteModel extends Model
{
    public function findById(int $id)
    {
        $subQuery = $this->db->table('m_rute m1')
            ->select("m1.*")
            ->where("m1.id", $id)
            ->getCompiledSelect();

        return $this->db->table('m_rute m')
            ->select("m.*")
            ->join("($subQuery) mt", 'mt.nama_rute = m.nama_rute')
            ->get()
            ->getResultArray();
    }
}

Solution 3A: Using A where Clause With A Closure.

where($key[, $value = null[, $escape = null]])
   Parameters:

      $key (mixed) – Name of field to compare, or associative array

      $value (mixed) – If a single key, compared to this value

      $escape (bool) – Whether to escape values and identifiers

   Returns: BaseBuilder instance (method chaining)

   Return type: BaseBuilder

Generates the WHERE portion of the query. Separates multiple calls with AND.

<?php

namespace App\Models;

use CodeIgniter\Model;
use CodeIgniter\Database\BaseBuilder;

class MRuteModel extends Model
{
    public function findById(int $id)
    {
        // With a closure.
        return $this->db->table('m_rute')
            ->where('nama_rute =', function (BaseBuilder $builder) use ($id) {
                return $builder->select("nama_rute")->from("m_rute")
                    ->where("id", $id);
            })
            ->get()
            ->getResultArray();
    }
}

Solution 3B: Using A where Clause With A Builder.

<?php

namespace App\Models;

use CodeIgniter\Model;

class MRuteModel extends Model
{
    public function findById(int $id)
    {
        $subQuery = $this->db->table('m_rute m1')
            ->select("m1.nama_rute")
            ->where("m1.id", $id)
            ->getCompiledSelect();

        // With a builder.
        return $this->db->table('m_rute')
            ->where("nama_rute =($subQuery)")
            ->get()
            ->getResultArray();
    }
}