Feature to not append database prefix in QueryBuilder

Why do you even need to skip DBPrefix if you have set it in the connection group?

I don't see the point of temporarily changing the DBPrefix during the connection.

As I said, you can create another connection group without a prefix and use it when you need it.

If I understand what you are suggesting correctly, you want me to create a custom connection group to handle no db prefix like this:

PHP Code:

$noPrefix = [
    'DSN'      => '',
    'hostname' => 'localhost',
    'username' => 'foo',
    'password' => 'bar',
    'database' => 'baz',
    'DBDriver' => 'MySQLi',
    'DBPrefix' => '',
    'pConnect' => false,
    'DBDebug'  => true,
    'charset'  => 'utf8mb4',
    'DBCollat' => 'utf8mb4_general_ci',
    'swapPre'  => '',
    'encrypt'  => false,
    'compress' => false,
    'strictOn' => false,
    'failover' => [],
    'port'     => 3306,
]; 


Then call $db = \Config\Database::connect($noPrefix); when needed. While generally that would resolve the problem, it doesn't resolve the problem where you need to selectively not add the prefix. For example, here is my current workaround

PHP Code:

function foreignKeyExists(string $constraintName, string $tableName): bool {

    $prefix = overridePrefix();

    $db = Database::connect();
    $builder = $db->table('INFORMATION_SCHEMA.TABLE_CONSTRAINTS');
    $builder->select('CONSTRAINT_NAME');
    $builder->where('TABLE_SCHEMA', $db->database);
    $builder->where('TABLE_NAME', $prefix . $tableName);
    $builder->where('CONSTRAINT_TYPE', 'FOREIGN KEY');
    $builder->where('CONSTRAINT_NAME', $constraintName);
    $query = $builder->get();

    overridePrefix($prefix);

    return $query->getNumRows() > 0;
} 


You are suggesting this change:

PHP Code:

function foreignKeyExists(string $constraintName, string $tableName): bool {
    $db = Database::connect('noPrefix');
    $builder = $db->table('INFORMATION_SCHEMA.TABLE_CONSTRAINTS');
    $builder->select('CONSTRAINT_NAME');
    $builder->where('TABLE_SCHEMA', $db->database);
    $builder->where('TABLE_NAME', $prefix . $tableName);
    $builder->where('CONSTRAINT_TYPE', 'FOREIGN KEY');
    $builder->where('CONSTRAINT_NAME', $constraintName);
    $query = $builder->get();

    return $query->getNumRows() > 0;
} 


The problem with this is that I don't need to remove the prefix in the entire query, just in the table. Notice my 2nd where statement depends on the tableName with a prefix. I'm requesting the feature to have table() allow not appending the prefix so the code becomes

PHP Code:

function foreignKeyExists(string $constraintName, string $tableName): bool {
    $db = Database::connect();
    $builder = $db->table('INFORMATION_SCHEMA.TABLE_CONSTRAINTS', false);
    $builder->select('CONSTRAINT_NAME');
    $builder->where('TABLE_SCHEMA', $db->database);
    $builder->where('TABLE_NAME', $tableName);
    $builder->where('CONSTRAINT_TYPE', 'FOREIGN KEY');
    $builder->where('CONSTRAINT_NAME', $constraintName);
    $query = $builder->get();

    return $query->getNumRows() > 0;
} 


And the signature to table() becomes

PHP Code:

public function table(string $tableName, string $appendPrefix = true) 


This would be a non-breaking change because the default value when $appendPrefix is not passed is true. That said, I respect that this may not be a straightforward change, but it would a useful change.