CodeIgniter 4.3.3 model - JOIN with LIKE and LIKEOR

Hello

may I kindly ask a hint about why this works but if I do enable the JOIN it doesn't work anymore? (follows after the code)

PHP Code:

    public function AjaxSearch($query)
    {
        if($query != '')
        {

            $this->like('email', $query);
            $this->orLike('name', $query);
            $this->orLike('subject', $query);
            $this->orLike('custom', $query);
            $this->orLike('note', $query);
            // $this->join('dept', 'dept.id = ticket.dept_id');
            $this->orderBy('lastactivity', 'DESC');

            return $this->findAll(5);
        }

        return;
    } 


this works like a charm and I get the 5 rows of fetched data from my table. But in the table there is one column that is dept_id
which is just an .. id so a meaningless number

I need to JOIN the 'dept' table to bind that id to the corresponding dept name and looks obvious that my approach is wrong

Can you kindly hint about the right approach? Should be managed in the controller? Or can be done here in the model?

Thank you

I would add dd((string)$this->getLastQuery()) to debug what query is actually produced.

PHP Code:

$result = $this->findAll(5);
dd((string)$this->getLastQuery()) 


The default join is INNER JOIN, so my first bet is there are no results that match the condition of the query. Try to use LEFT JOIN.

PHP Code:

$this->join('dept', 'dept.id = ticket.dept_id', 'left'); 


I would add dd((string)$this->getLastQuery()) to debug what query is actually produced.

PHP Code:

$result = $this->findAll(5);
dd((string)$this->getLastQuery()) 


The default join is INNER JOIN, so my first bet is there are no results that match the condition of the query. Try to use LEFT JOIN.

PHP Code:

$this->join('dept', 'dept.id = ticket.dept_id', 'left'); 


Thank you Michal

getLastQuery() has been decisive at debug level , I ended up discovering that the JOIN was not working because there was an ambiguity of two columns with same name in 'dept' and 'ticket' table

INNER JOIN is good because of the 'dept_id' univocity