CodeIgniter 4 Inner Join in Codeigniter-CodePudding

Just want to ask if how can I use the INNER JOIN function on CodeIgniter 4? I'm trying to convert the SQL statement below to CodeIgniter 4 but since I'm just new in this area, I find it diffucult.

SELECT l.l_id, u.fname, u.mname, u.lname, u.dept, u.user_type, u.u_grdsec, e.e_name, l.date_added, DATE(l.date_added), date_format(l.date_added, '%H:%i') as 'time'
FROM logs l INNER JOIN users u ON l.u_id = u.u_id
INNER JOIN establishment e ON l.e_id = e.e_id
WHERE DATE(l.date_added) = '$rep_date'
GROUP BY
   date_format(l.date_added, '%H:%i'), l.u_id
ORDER BY l.date_added ASC";

As I researched, I find this way. However, I didn't know how can I put the WHERE, GROUP BY and ORDER by clause in there.

$builder = $this->db->table("logs as l");
$builder->select('l.l_id, u.fname, u.mname, u.lname, u.dept, u.user_type, u.u_grdsec, e.e_name, l.date_added, DATE(l.date_added), date_format(l.date_added, "%H:%i") as "time"');
$builder->join('users as u', 'l.u_id = u.u_id');
$builder->join('establishment as e', 'l.e_id = e.e_id');
$data = $builder->get()->getResult();

Another thing that confused me is I don't know where should I put it. I don't know if I should put it on the Controller or on the Model file.

So far, here's the code on my Controller.

public function getLogs(){
helper(['form']);
$model = new LogsMod();
$rep_est = $this->request->getPost('rep_est');
$rep_date = $this->request->getPost('rep_date');
$data['logs_data'] = $model->where('DATE(l.date_added)', $rep_date)->groupBy(date_format(l.date_added, '%H:%i'), l.u_id)->orderBy('l.date_added', 'ASC')->findAll();
return view()->to('LogsReport', $data);
}

And here the code on my Model.

public function getLogs(){

$builder = $this->db->table("logs as l");
$builder->select('l.l_id, u.fname, u.mname, u.lname, u.dept, u.user_type, u.u_grdsec, e.e_name, l.date_added, DATE(l.date_added), date_format(l.date_added, "%H:%i") as "time"');
$builder->join('users as u', 'l.u_id = u.u_id');
$builder->join('establishment as e', 'l.e_id = e.e_id');
$data = $builder->get()->getResult();
}

However, I don't think that the two are connected.

CodePudding user response：

CodeIgniter 4 Models are themself builders. You can specify the db table using the class variable

protected $table = '<your table>';

in classes extending CodeIgniter\Model. When building queries in a function you can refer to a builder simply using $this. So your function could look like this:

public function getLogs(){
    $this->select('l.l_id, u.fname, u.mname, u.lname, u.dept, u.user_type, u.u_grdsec, e.e_name, l.date_added, DATE(l.date_added), date_format(l.date_added, "%H:%i") as "time"');
    $this->join('users as u', 'l.u_id = u.u_id');
    $this->join('establishment as e', 'l.e_id = e.e_id');
    $this->where(...);
    $this->orderBy(...);
    return $this->findAll();
}

The where() method accepts key-value arrays or couples of variables/strings:

$this->where(['key1' => 'value1','key2' => 'value2' ]);
// or
$this->where('key1', 'value1');
$this->where('key2', 'value2');

where key1 and key2 are your database table fields.

Note that in your controller you should instantiate your model object using the model() function:

$model = model(LogsMod::class);