How to use table alias in codeigniter 4?

Question

I'm trying to join religions and citizen tables inside the CitizenModel. The problem is the from statement always includes the table citizen.

Here's the sample Model script:

<?php

namespace App\Models;
use CodeIgniter\Model;

class CitizenModel extends Model
{
  protected $table = 'citizen';
  protected $primaryKey = 'CitizenID';
  protected $allowedFields = [
    'CitizenID',
    'ReligionId',
    ];
    
  public function getCitizen()
  {
    //$this->distinct();
    $this->select('a.*, b.Name as Religion');
    $this->from("citizen a");
    $this->join('religions b', 'b.ReligionId = a.ReligionId', 'LEFT');

    $result = $this->findAll();
    echo $this->db->getLastQuery();
    return $result;
  }
}

Here is the lastQuery result:

SELECT `a`.*, `b`.`Name` as `Religion`
FROM (`citizen`, `citizen` `a`)
LEFT JOIN `religions` `b` ON `b`.`ReligionId` = `a`.`ReligionId`

I tested the query in sqlyog but query result for

FROM (`citizen`, `citizen` `a`)

is different from

FROM (`citizen` `a`)

Unless I added the DISTINCT in the select.

Answer 1

I think this happens because you're actually setting two selects. One in the $table property and one in your $this->from method.

What I would try to do in this case was remove the $this->from and re-write the $table property. Something like this:

  public function getCitizen()
  {
    //$this->distinct();
    $this->select('a.*, b.Name as Religion');
    $this->table("citizen a");
    $this->join('religions b', 'b.ReligionId = a.ReligionId', 'LEFT');

    $result = $this->findAll();
    echo $this->db->getLastQuery();
    return $result;
  }

If that does not work you might want to use a different database instance for that query instead of the one being use by the model. Using the query builder.

public function getCitizen() {
    $db = \Config\Database::connect();
    $builder = $db->table('citizen a');
    $builder->select('a.*, b.Name as Religion');
    $builder->join('religions b', 'b.ReligionId = a.ReligionId', 'LEFT');
    $result = $builder->get()->getResult();
}