Problem with codeigniter 4 Query builder classes

Question

On terminal, in mysql , running the following query gives this result

mysql> SELECT DISTINCT(city) FROM outlets_data;
+-----------+
| city      |
+-----------+
| Paris     |
| New York  |
| Kolkata   |
| Moscow    |
| Mumbai    |
| Hyderabad |
| Delhi     |
| Chennai   |
+-----------+
8 rows in set (0.00 sec)

I want to store the names of these cities, in an array, in codeigniter 4 models class file.

Models/DashboardModels.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class DashboardModel extends Model
{
    protected $table      = 'outlets_data';
    protected $primaryKey = 'shop_id';

    public function not_defined_yet()
    {
        $city_names = $this->select('city')->distinct(); // This should be equivalent to "SELECT DISTINCT(city) FROM outlets_data";
        
        return $city_names;
    }
}

Controller/Home.php

<?php

namespace App\Controllers;

use App\Models\DashboardModel;

use CodeIgniter\Model;

class Home extends BaseController
{
    public function index()
    {
        $model = new DashboardModel();
        $data['undefined'] = $model->not_defined_yet();

        echo view('dashboard', $data);
    }
}

Views/Dashboard.php

 <?php echo "<pre>";  print_r($undefined); echo "</pre>"; ?>

I expect to get names of the cities in output array, but I am getting whole database as associative array.

Answer 1

Your function should be:

public function not_defined_yet()
{
    $city_names = $this->select('city')->distinct(); // This should be equivalent to "SELECT DISTINCT(city) FROM outlets_data";
    
    return $this;
}

Then your function be

$data['undefined'] = $model->not_defined_yet()->findAll();

Other way you can do it is loading a new instance of the database object.

public function not_defined_yet()
{

    $db         = \Config\Database::connect();
    $builder    = $db->table('outlets_data');
    $city_names = $builder->select('city')->distinct(); 
    
    return $city_names->resultArray();
}

You can even remove the function all together and in your controller do this:

$data['undefined'] = $model->select('city')->distinct()->findAll();

This would get the same exact result.