Database interaction through Codeigniter 4 model

Dear friends,

I have two tables and they have some columns matching in name. The tables are "ticket" and "dept"

Both do have "id" and "name" columns

My approach is to use the Codeigniter model

so I have TicketModel

PHP Code:

<?php

namespace App\Models;

class TicketModel extends \CodeIgniter\Model
{
    protected $table = 'ticket';

} 


and I have TicketController

PHP Code:

<?php 

namespace App\Controllers;

class TicketController extends BaseController
{

public function showTickets()
{

$model = new \App\Models\TicketModel();

$data = $model ->where('ticket.dept_id', '52')
               ->join('dept', 'ticket.dept_id = dept.id')
               ->orderBy('ticket.date', 'desc')
               ->findAll();

dd($data); 


everything works, but dd($data); shows that the join result show only one "id" column and only one "name" column. To be precise, the id and the name columns are filled with the 'dept' table values .. but I need also ticket.id and ticket.name columns

This should be solved with MySQL aliases

But... where/how do I insert the aliases statement? meant working with the approach

PHP Code:

$model = new \App\Models\TicketModel(); 


, thank you for hinting

1. Do not make queries to the database in the controller. This is a violation of the MVC pattern. Requests must be in the model.
2. Use the select() method.

Hello thank you for the hint

The problem for me was to make the right use of alias for columns

Easy task for experienced coders ... but I'm not and experienced coder so ..

For others newbies I write what I have found as solution

PHP Code:

$data = $ticketModel ->select('ticket.id, ticket.name as t_name, ticket.date, dept.name')
                     ->where('ticket.dept_id', '52')
                     ->join('dept', 'ticket.dept_id = dept.id')
                     ->orderBy('ticket.date', 'desc')
                     ->findAll();

dd($data); 


as suggested I moved the DB interaction into the model

PHP Code:

<?php

namespace App\Models;

class TicketModel extends \CodeIgniter\Model
{

    protected $table = 'ticket'; 


    public function getTicketsByDept($deptID)
{

$ticketModel = new TicketModel();

$data = $ticketModel ->select('ticket.id, ticket.name as t_name, ticket.date, dept.name as d_name')
                     ->where('ticket.dept_id', $deptID)
                     ->join('dept', 'ticket.dept_id = dept.id')
                     ->orderBy('ticket.date', 'asc')
                     ->findAll();
      
        return $data;

    }
    
} 


PHP Code:

<?php

namespace App\Models;

class TicketModel extends \CodeIgniter\Model
{

    protected $table = 'ticket'; 


    public function getTicketsByDept($deptID)
{

$ticketModel = new TicketModel();

$data = $ticketModel ->select('ticket.id, ticket.name as t_name, ticket.date, dept.name as d_name')
                     ->where('ticket.dept_id', $deptID)
                     ->join('dept', 'ticket.dept_id = dept.id')
                     ->orderBy('ticket.date', 'asc')
                     ->findAll();
      
        return $data;

    }
    
} 


You create instance of TicketModel in instance of TicketModel. 

@iRedds

GULP eheheheheh thank you

Cuttin' 'n pasting the code in the model... the mistake was behind the corner