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.

marcogmonteiro · Accepted Answer · 2021-04-12T07:57:38.717

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();
}

I think it doesn't work. I removed the from and added the `$this->table("citizen a");` but it returns `FROM `citizen`` only, juslt like the default. — unice, Apr 12 '21 at 07:46
Then you might want to use another database instance instead of the one being used by the model. I'll add that option to the answer. — marcogmonteiro, Apr 12 '21 at 07:54

score 0 · Answer 2 · answered Aug 16 '22 at 03:46

Try to reset "from" statement.

Using this line you can reset the from statement

->from([], true)

and redefine like

->from('gk_communication_group gkcg')

For example

public function getAll(){
    $tenant = TENANT;
    return $this
        ->from([], true)
        ->from('gk_communication_group gkcg')
        ->select("
            gkcg.id, gkcg.name, gkcg.description, 
            (SELECT count(*) 
                FROM gk_communication_group_item gki 
                WHERE gki.group=gkcg.id) as count")
        ->where("gkcg.tenant", TENANT)->get()->getResult();
}

How to use table alias in codeigniter 4?

2 Answers2