[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
CodePudding user response:
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 returnstrue
orfalse
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.
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 withAND
.
<?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();
}
}