CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecordquery-builder
ramboeistblast
ramboeistblast

Reputation: 55

Convert raw MySQL containing a JOIN on identical column names and a WHERE and ORDER BY clause to CodeIgniter active record

I have a MySQL query like this:

SELECT * FROM ms_project_log 
INNER JOIN ms_project ON ms_project_log.iwo_no = ms_project.iwo_no
WHERE  ms_project_log.iwo_no = '0007/NMS/BOTM/01/12'
ORDER BY ms_project_log.log_date DESC

How to convert it to CodeIgniter active record code?

Upvotes: 1

Views: 206

Answers (3)

mickmackusa
mickmackusa

Reputation: 47864

Because the columns used to join the two tables are identically names, the USING keyword can be used instead of ON.

from(), where(), and get() can be consolidated into a single get_where() method call.

->select('*') is never needed in a CodeIgniter script, it is the default setting for the SELECT clause.

->result() will return an array of zero or more objects.

Model method body:

public function getProjectLogsByNo(string $iwoNo): array
{
    return $this->db
        ->join('ms_project', 'iwo_no')
        ->order_by('log.log_date', 'DESC')
        ->get_where(
            'ms_project_log log',
            ['log.iwo_no' => $iwoNo]
        )
        ->result();
}

Rendered SQL:

SELECT *
FROM `ms_project_log` `log`
JOIN `ms_project` USING (`iwo_no`)
WHERE `log`.`iwo_no` = '0007/NMS/BOTM/01/12'
ORDER BY `log`.`log_date` DESC

Upvotes: 0

Yanuar Lutfi
Yanuar Lutfi

Reputation: 153

$this->db->join('ms_project','ms_project_log.iwo_no = ms_project.iwo_no','inner')
         ->order_by('ms_project_log.log_date','desc')
         ->get_where('ms_project_log', array('ms_project_log.iwo_no'=>'0007/NMS/BOTM/01/12'))
         ->result_array();

You can filter selected column using select method, by default if not using select method your query will use "select *"

Upvotes: 0

mamdouh alramadan
mamdouh alramadan

Reputation: 8528

here's a code for you

$this->db->select('*');
$this->db->from('ms_project_log');
$this->db->join('ms_project','ms_project_log.iwo_no=ms_project_iwo_no', 'inner');
$this->db->where(ms_project_log.iwo_no, '0007/NMS/BOTM/01/12');
$this->db->order_by("ms_project_log.log_date", "desc"); 
$this->db->get();

Upvotes: 1

Related Questions