Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bug: Query builder with table prefix after updating to 4.6.0 version #9424

Closed
pioneersingh321 opened this issue Jan 20, 2025 · 5 comments
Closed

Comments

@pioneersingh321
Copy link

pioneersingh321 commented Jan 20, 2025

PHP Version

8.2

CodeIgniter4 Version

4.6.0

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Windows

Which server did you use?

apache

Database

MySQL 5.6

What happened?

Its adding
database.default.DBPrefix = h_

in every filed and in where condition its adding the table prefix

event with the table

this is quire Builder

            $builder->select(['e.id','e.employee_number','e.employee_prefix','e.first_name','e.middle_name','e.last_name','e.is_deleted','e.contact_number','e.email']);
            
           $builder->where('e.is_deleted',0);

            $builder->join('location as l', 'l.id = e.location_id')->select('l.name as location_name');

            $builder->select(new RawSql("CONCAT(`first_name`, ' ', `middle_name`,' ',`last_name`) as full_name"));
            $builder->select(new RawSql("CONCAT(`employee_prefix`, '', `employee_number`) as full_number"));

            return $builder;

Steps to Reproduce

{
    "title": "CodeIgniter\\Database\\Exceptions\\DatabaseException",
    "type": "CodeIgniter\\Database\\Exceptions\\DatabaseException",
    "code": 500,
    "message": "Unknown column 'h_e.id' in 'field list'",
    "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\vendor\\codeigniter4\\framework\\system\\Database\\BaseConnection.php",
    "line": 692,
    "trace": [
        {
            "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\vendor\\codeigniter4\\framework\\system\\Database\\MySQLi\\Connection.php",
            "line": 327,
            "function": "query",
            "class": "mysqli",
            "type": "->",
            "args": [
                "SELECT `h_e`.`id`, `h_e`.`employee_number`, `h_e`.`employee_prefix`, `h_e`.`first_name`, `h_e`.`middle_name`, `h_e`.`last_name`, `h_e`.`is_deleted`, `h_e`.`contact_number`, `h_e`.`email`, `h_l`.`name` as `location_name`, CONCAT(`first_name`, ' ', `middle_name`,' ',`last_name`) as full_name, CONCAT(`employee_prefix`, '', `employee_number`) as full_number\nFROM `h_employee` as `e`\nJOIN `h_location` as `l` ON `l`.`id` = `e`.`location_id`\nWHERE e.is_deleted=0\n LIMIT 10",
                0
            ]
        },
        {
            "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\vendor\\codeigniter4\\framework\\system\\Database\\BaseConnection.php",
            "line": 738,
            "function": "execute",
            "class": "CodeIgniter\\Database\\MySQLi\\Connection",
            "type": "->",
            "args": [
                "SELECT `h_e`.`id`, `h_e`.`employee_number`, `h_e`.`employee_prefix`, `h_e`.`first_name`, `h_e`.`middle_name`, `h_e`.`last_name`, `h_e`.`is_deleted`, `h_e`.`contact_number`, `h_e`.`email`, `h_l`.`name` as `location_name`, CONCAT(`first_name`, ' ', `middle_name`,' ',`last_name`) as full_name, CONCAT(`employee_prefix`, '', `employee_number`) as full_number\nFROM `h_employee` as `e`\nJOIN `h_location` as `l` ON `l`.`id` = `e`.`location_id`\nWHERE e.is_deleted=0\n LIMIT 10"
            ]
        },
        {
            "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\vendor\\codeigniter4\\framework\\system\\Database\\BaseConnection.php",
            "line": 652,
            "function": "simpleQuery",
            "class": "CodeIgniter\\Database\\BaseConnection",
            "type": "->",
            "args": [
                "SELECT `h_e`.`id`, `h_e`.`employee_number`, `h_e`.`employee_prefix`, `h_e`.`first_name`, `h_e`.`middle_name`, `h_e`.`last_name`, `h_e`.`is_deleted`, `h_e`.`contact_number`, `h_e`.`email`, `h_l`.`name` as `location_name`, CONCAT(`first_name`, ' ', `middle_name`,' ',`last_name`) as full_name, CONCAT(`employee_prefix`, '', `employee_number`) as full_number\nFROM `h_employee` as `e`\nJOIN `h_location` as `l` ON `l`.`id` = `e`.`location_id`\nWHERE e.is_deleted=0\n LIMIT 10"
            ]
        },
        {
            "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\vendor\\codeigniter4\\framework\\system\\Database\\BaseBuilder.php",
            "line": 1649,
            "function": "query",
            "class": "CodeIgniter\\Database\\BaseConnection",
            "type": "->",
            "args": [
                "SELECT `h_e`.`id`, `h_e`.`employee_number`, `h_e`.`employee_prefix`, `h_e`.`first_name`, `h_e`.`middle_name`, `h_e`.`last_name`, `h_e`.`is_deleted`, `h_e`.`contact_number`, `h_e`.`email`, `h_l`.`name` as `location_name`, CONCAT(`first_name`, ' ', `middle_name`,' ',`last_name`) as full_name, CONCAT(`employee_prefix`, '', `employee_number`) as full_number\nFROM `h_employee` as `e`\nJOIN `h_location` as `l` ON `l`.`id` = `e`.`location_id`\nWHERE e.is_deleted=0\n LIMIT 10",
                [],
                false
            ]
        },
        {
            "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\app\\Libraries\\Datatables.php",
            "line": 311,
            "function": "get",
            "class": "CodeIgniter\\Database\\BaseBuilder",
            "type": "->",
            "args": []
        },
        {
            "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\app\\Controllers\\Api\\Datatable.php",
            "line": 28,
            "function": "generate",
            "class": "App\\Libraries\\Datatables",
            "type": "->",
            "args": []
        },
        {
            "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\vendor\\codeigniter4\\framework\\system\\CodeIgniter.php",
            "line": 933,
            "function": "postEmployee",
            "class": "App\\Controllers\\Api\\Datatable",
            "type": "->",
            "args": []
        },
        {
            "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\vendor\\codeigniter4\\framework\\system\\CodeIgniter.php",
            "line": 507,
            "function": "runController",
            "class": "CodeIgniter\\CodeIgniter",
            "type": "->",
            "args": [
                {}
            ]
        },
        {
            "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\vendor\\codeigniter4\\framework\\system\\CodeIgniter.php",
            "line": 354,
            "function": "handleRequest",
            "class": "CodeIgniter\\CodeIgniter",
            "type": "->",
            "args": [
                null,
                {
                    "handler": "file",
                    "backupHandler": "dummy",
                    "prefix": "",
                    "ttl": 60,
                    "reservedCharacters": "{}()/\\@:",
                    "file": {
                        "storePath": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\writable\\cache/",
                        "mode": 416
                    },
                    "memcached": {
                        "host": "127.0.0.1",
                        "port": 11211,
                        "weight": 1,
                        "raw": false
                    },
                    "redis": {
                        "host": "127.0.0.1",
                        "password": null,
                        "port": 6379,
                        "timeout": 0,
                        "database": 0
                    },
                    "validHandlers": {
                        "dummy": "CodeIgniter\\Cache\\Handlers\\DummyHandler",
                        "file": "CodeIgniter\\Cache\\Handlers\\FileHandler",
                        "memcached": "CodeIgniter\\Cache\\Handlers\\MemcachedHandler",
                        "predis": "CodeIgniter\\Cache\\Handlers\\PredisHandler",
                        "redis": "CodeIgniter\\Cache\\Handlers\\RedisHandler",
                        "wincache": "CodeIgniter\\Cache\\Handlers\\WincacheHandler"
                    },
                    "cacheQueryString": false
                },
                false
            ]
        },
        {
            "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\vendor\\codeigniter4\\framework\\system\\Boot.php",
            "line": 334,
            "function": "run",
            "class": "CodeIgniter\\CodeIgniter",
            "type": "->",
            "args": []
        },
        {
            "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\vendor\\codeigniter4\\framework\\system\\Boot.php",
            "line": 67,
            "function": "runCodeIgniter",
            "class": "CodeIgniter\\Boot",
            "type": "::",
            "args": [
                {}
            ]
        },
        {
            "file": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\public\\index.php",
            "line": 56,
            "function": "bootWeb",
            "class": "CodeIgniter\\Boot",
            "type": "::",
            "args": [
                {
                    "systemDirectory": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\app\\Config/../../vendor/codeigniter4/framework/system",
                    "appDirectory": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\app\\Config/..",
                    "writableDirectory": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\app\\Config/../../writable",
                    "testsDirectory": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\app\\Config/../../tests",
                    "viewDirectory": "D:\\mamp\\suvidha\\ci_hrms_dashboard\\app\\Config/../Views"
                }
            ]
        }
    ]
}

Expected Output

its should not add the h_ table prefix in where cause as i am using table alias

Anything else?

No response

@pioneersingh321 pioneersingh321 added the bug Verified issues on the current code behavior or pull requests that will fix them label Jan 20, 2025
@pioneersingh321 pioneersingh321 changed the title Bug: Q builder with Bug: Query builder with table prefix after updating to 4.6.0 version Jan 20, 2025
@michalsn
Copy link
Member

Sorry, but I have not been able to reproduce the problem using the steps given:

<?php

namespace App\Controllers;

use CodeIgniter\Database\RawSql;

class Home extends BaseController
{
    public function index()
    {
        //return view('welcome_message');

        $builder = db_connect()->table('employee as e');

        $builder->select(['e.id','e.employee_number','e.employee_prefix','e.first_name','e.middle_name','e.last_name','e.is_deleted','e.contact_number','e.email']);

        $builder->where('e.is_deleted',0);

        $builder->join('location as l', 'l.id = e.location_id')->select('l.name as location_name');

        $builder->select(new RawSql("CONCAT(`first_name`, ' ', `middle_name`,' ',`last_name`) as full_name"));
        $builder->select(new RawSql("CONCAT(`employee_prefix`, '', `employee_number`) as full_number"));

        dd($builder->getCompiledSelect());
    }
}

This gives me:

SELECT `e`.`id`, `e`.`employee_number`, `e`.`employee_prefix`, `e`.`first_name`, `e`.`middle_name`, `e`.`last_name`, `e`.`is_deleted`, `e`.`contact_number`, `e`.`email`, `l`.`name` as `location_name`, CONCAT(`first_name`, ' ', `middle_name`,' ',`last_name`) as full_name, CONCAT(`employee_prefix`, '', `employee_number`) as full_number
FROM `h_employee` as `e`
JOIN `h_location` as `l` ON `l`.`id` = `e`.`location_id`
WHERE `e`.`is_deleted` = 0

Please provide a better explanation/example so that we can reproduce the problem.

@michalsn michalsn removed the bug Verified issues on the current code behavior or pull requests that will fix them label Jan 20, 2025
@pioneersingh321
Copy link
Author

in .env file i gave database.default.DBPrefix = h_

then in query builder
$builder = $db->table->('employee as e')->where('e.is_delete',0)->get()->getResultArray()

its throwing error h_e.is_delete is not a field in table
its adding prefix in where and select condition

@michalsn
Copy link
Member

database.default.DBPrefix = h_

is set - as you may see in the produced SQL.

@pioneersingh321
Copy link
Author

its fix .. its was showing because i was using datatable library error was due to it

@michalsn
Copy link
Member

Thank you for the info.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants