Data does not appear if fetched from table name with dot symbol

[rachyharkov]

Bug Description

All data from query result wont simply return the all column of the data, resulting only show the data which is came from add() method (but the amount of data is correct).

As my investigation goes, this is caused by table which is placed on different schema (for ex: the data shown up if the table is from "public" schema instead of "other_public" schema)

My Code

In controller

$db = db_connect();
$query = $this->table('other_public.users')

return DataTable::of($builder)
                ->add('my_custom_field', function($row) {
                    return 'some text'
                })
                ->toJson(true);
 

if using model, here is what it looks like:

Model

class Announcement extends Model
{
    protected $DBGroup          = 'default';
    protected $table            = 'cms.announcements';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'object';

}

Controller

class Announcement extends BaseController
{
    protected $announcement_model;

    function __construct()
    {
        $this->announcement_model = new ModelsAnnouncement();
    }

    public function index()
    {
            return DataTable::of($query)
                ->add('my_custom_field', function($row) {
                    return 'some text';
                })
                ->toJson(true);
    }
}

Screenshots

but if you check the query result using dd or whatever, it shows all the data

• PHP Version: 7.4
• CodeIgniter version: 4.3.6
• Library version: 0.6.1

[rachyharkov]

Looks like the problem come from this function, since the query result shows all the column data

https://github.com/hermawanramadhan/CodeIgniter4-DataTables/blob/35804670f315f8eba27d58366bbef22fb01d8d37/src/DataTableQuery.php#L68

if i change the code like this

public function getDataResult()
{
    $queryResult = $this->queryResult();

    return $queryResult;
}

it shows as expected, but without the value from add, edit, etc.

[rachyharkov]

it leads me to this function

https://github.com/hermawanramadhan/CodeIgniter4-DataTables/blob/35804670f315f8eba27d58366bbef22fb01d8d37/src/DataTableColumnDefs.php#L238

on the first conditional, on else block, if the table are from other scheme, the table name have double quotes on each which is separated by dot

Here is the result:

if i'm changing to table without any schema defined, like this..

this is the result:

so i'm trying to debug how does getFieldData works since it was a part of Codeigniter 4 it self

public function index()
{
    $db = db_connect();
    $builder = $db->getFieldData('cms.announcements');
    dd($builder);
}

Here is the result...

Well, looks like it doesn't need to include the schema name (even with dot, just table name), and here is the result...

public function index()
{
    $db = db_connect();
    $builder = $db->getFieldData('announcements');
    dd($builder);
}

[rachyharkov]

i have proposed the PR #38

[hermawanramadhan]

did you try schema on config? https://codeigniter.com/user_guide/database/configuration.html

I'm worried that if your pull request is applied it will damage other database drivers..

[rachyharkov]

in my case, i have some connection that depends on other database type (mysqly postgre, and oraclr), i need to still define the dot on option which is required to choose the table by name (especially postgre which is linked to schema)

the code i've applied just doing the general task (detecting dots, if the string are looks normal, just leave it be)

i know that soo many of library user are reliying on mySQL, if they saw the similar problem, they just simply use another lib

honestly, i'm not forcing you to accept my pr, just making use of this lib a bit easier, readable, and understandable, i have spend my time doing the test based on the changes i made, soo i was thinking it's "optionally good" implement those changes

you might need to test it out too

it's interesting to know more about the config, which option that i should set? and what value should i define though?

ps: sorry i'm not on my laptop at the moment right now

[hermawanramadhan]

I've never tried postgresql

I mean db config whatever on php or .env Documentation on CodeIgniter you can add schema option on db config

[rachyharkov]

sorry for having kept you waiting, thanks for suggestion though, i tried tinkering with all of those configuration... nothing met my expectation, the best i can do is modify the base code based on my proposed PR (i was thinking at the beginning, maybe it works because the way of codeigniter is create new connection with new schema, but it fails)

and there's another problem too, while doing search (fetched from non-public schema) the error shown up since the key pressed

i'll try to find what causing this but i'm 100% percent sure the lib base code should handle these behavior might be a good benefit...