Bug: Join Query is not working in Library

[SyedMuradAliShah]

Description
I think its a bug as well, using the query builder, the join query is not working! It gets only the table mention inside table() i.e acl_roles_permissions, while in MySQL Manager I'm getting the result with join.

$db = \Config\Database::connect();
$res = $db->table('acl_roles_permissions')
     ->select('`acl_roles_permissions`.`role_id` as `permission_role_id`,`acl_roles_permissions`.`permission_id`, `permissions`.`key` as `permission_key`,`permissions.name` as `permission_name`')
     ->join('acl_permissions permissions', 'permissions.id = acl_roles_permissions.permission_id')
     ->where('acl_roles_permissions.role_id', $role_id);
if ($res->countAllResults() > 0) {
     print_r($res->get()->getResultArray());

Result of above code in the library

The same query runs inside MySQL manager.

CodeIgniter 4 version
4.0.4

Context

• Windows 10
• PHP version 7.2.19

[michalsn]

You're using Builder class incorrectly and you're not creating the query you think of. Please read more about Builder class in the user guide: https://codeigniter4.github.io/userguide/database/query_builder.html#selecting-data

Hint - it should be:

...
$res = $db->table('acl_roles_permissions');
$res->select(...

[SyedMuradAliShah]

You're using Builder class incorrectly and you're not creating the query you think of. Please read more about Builder class in the user guide: https://codeigniter4.github.io/userguide/database/query_builder.html#selecting-data

Hint - it should be:

...
$res = $db->table('acl_roles_permissions');
$res->select(...

Well I verified the query $res->getCompiledSelect(); the query I received is:

SELECT acl_roles_permissions.role_id AS permission_role_id, acl_roles_permissions.permission_id, permissions.key AS permission_key, permissions.name AS permission_name FROM acl_roles_permissions JOIN acl_permissions permissions ON permissions.id = acl_roles_permissions.permission_id WHERE acl_roles_permissions.role_id = '1'

But I'm unable to get the result, of joined queries. Btw the Hint you gave is the same as my code, Put the whole builder query in one statement.

For my problem I tried another way I used $db->query()

            $query = $res->getCompiledSelect();
            print_r($db->query($query)->getResultArray());

With this, I fixed my problem. But still, we can not get the result of the joined query using the code in my question. Please verify it once.

[michalsn]

Yes, you're right, but still, it's not a bug. Please try:

if ($res->countAllResults(false) > 0) {
     print_r($res->get()->getResultArray());

[SyedMuradAliShah]

Yes, you're right, but still, it's not a bug. Please try:

if ($res->countAllResults(false) > 0) {
     print_r($res->get()->getResultArray());

Again failed, its result is the same i.e without join queries data.

[michalsn]

Sorry that this is not working for you, but I actually build a similar join query locally and I was not able to recreate the issue you're describing. We also have tests in place and they work fine too.

Please post your problem in the Forum since it doesn't seem like a bug in the framework.