Problems with Query Builder thinking my tables/columns are column values

So I have a somewhat complex (and ugly) query that I'm using on my application. I recently made some changes to allow users who eventually download and use my app to supply a database prefix.

Before I made the changes, here was the query (I know its an abomination):

PHP Code:

self::$db->select('groups.group_id, groups.group_id as gid, groups.name, groups.description, groups.enabled,'

    . '(SELECT group_concat(accounts.username SEPARATOR ",") FROM accounts JOIN accounts_group_assoc ON accounts_group_assoc.account_id = accounts.account_id WHERE accounts_group_assoc.group_id = groups.group_id) as accounts,'

    . '(SELECT group_concat(group_partition_assoc.partition_id SEPARATOR ",") FROM group_partition_assoc WHERE group_partition_assoc.group_id = gid) as partition_ids,'

    . '(SELECT group_concat(distinct partitions.name  SEPARATOR ",") FROM partitions JOIN group_partition_assoc ON group_partition_assoc.partition_id = partitions.partition_id WHERE group_partition_assoc.group_id = gid) as partition_names,'

    . '(SELECT group_concat(roles.role_id SEPARATOR ",") FROM roles JOIN roles_groups_assoc ON roles_groups_assoc.role_id = roles.role_id WHERE roles_groups_assoc.group_id = groups.group_id) as role_ids,'

    . '(SELECT group_concat(roles.name SEPARATOR ",") FROM roles JOIN roles_groups_assoc ON roles_groups_assoc.role_id = roles.role_id WHERE roles_groups_assoc.group_id = groups.group_id) as roles', FALSE);

if(isset($group))
    if( ! is_array($group))
        if(is_numeric($group))
            self::$db->where("groups.group_id", $group);
        else
            self::$db->where("groups.name", $group);
    else
        self::$db->where_in("groups.group_id", $group);

$query = self::$db->get('groups'); 


I revised the query, taking advantage of the get_compiled_select() method, and heres the revised version:

PHP Code:

$accounts_select = self::$db
    ->select("group_concat({$this->_p('accounts')}.username SEPARATOR ',')")
    ->from('accounts')
    ->join("accounts_group_assoc", "accounts_group_assoc.account_id = accounts.account_id")
    ->where("{$this->_p('accounts_group_assoc')}.group_id","{$this->_p('groups')}.group_id")
    ->get_compiled_select();

$partition_ids_select = self::$db
    ->select("group_concat({$this->_p('group_partition_assoc')}.partition_id SEPARATOR ',')")
    ->from('group_partition_assoc')
    ->where("{$this->_p('group_partition_assoc')}.group_id","gid")
    ->get_compiled_select();

$partition_names_select = self::$db
    ->select("group_concat(distinct {$this->_p('partitions')}.name SEPARATOR ',')")
    ->from('partitions')
    ->join("group_partition_assoc", "group_partition_assoc.partition_id = partitions.partition_id")
    ->where("{$this->_p('group_partition_assoc')}.group_id","gid")
    ->get_compiled_select();

$role_ids_select = self::$db
    ->select("group_concat({$this->_p('roles')}.role_id SEPARATOR ',')")
    ->from('roles')
    ->join("roles_groups_assoc", "roles_groups_assoc.role_id = roles.role_id")
    ->where("{$this->_p('roles_groups_assoc')}.group_id","{$this->_p('groups')}.group_id")
    ->get_compiled_select();

$role_select = self::$db
    ->select("group_concat({$this->_p('roles')}.name SEPARATOR ',')")
    ->from('roles')
    ->join("roles_groups_assoc", "roles_groups_assoc.role_id = roles.role_id")
    ->where("{$this->_p('roles_groups_assoc')}.group_id","{$this->_p('groups')}.group_id")
    ->get_compiled_select();

self::$db
    ->select('groups.group_id, groups.group_id as gid, groups.name, groups.description, groups.enabled')
    ->select("({$accounts_select}) as accounts")
    ->select("({$partition_ids_select}) as partition_ids")
    ->select("({$partition_names_select}) as partition_names")
    ->select("({$role_ids_select}) as role_ids")
    ->select("({$role_select}) as roles");


if(isset($group))
    if( ! is_array($group))
        if(is_numeric($group))
            self::$db->where("{$this->_p('groups')}.group_id", $group);
        else
            self::$db->where("{$this->_p('groups')}.name", $group);
    else
        self::$db->where_in("{$this->_p('groups')}.group_id", $group);

$query = self::$db->get('groups'); 


The query executes without any errors, however, the values of the selects with the sub queries are always NULL. I used the db::last_query () method to get the query string that was ran.

Here is how it SHOULD look:

Code:

SELECT
 `appdb_groups`.`group_id`,
 `appdb_groups`.`group_id` as `gid`,
 `appdb_groups`.`name`,
 `appdb_groups`.`description`,
 `appdb_groups`.`enabled`,
 (
   SELECT
 group_concat(appdb_accounts.username SEPARATOR ', ')
   FROM
     `appdb_accounts`
     JOIN
     `appdb_accounts_group_assoc` ON `appdb_accounts_group_assoc`.`account_id` = `appdb_accounts`.`account_id`
   WHERE
     `appdb_accounts_group_assoc`.`group_id` = appdb_groups.group_id
 ) as accounts,
 (
   SELECT
 group_concat(appdb_group_partition_assoc.partition_id SEPARATOR ', ')
   FROM
     `appdb_group_partition_assoc`
   WHERE
     `appdb_group_partition_assoc`.`group_id` = gid
 ) as partition_ids,
 (
   SELECT
 group_concat(distinct appdb_partitions.name SEPARATOR ', ')
   FROM
     `appdb_partitions`
     JOIN
     `appdb_group_partition_assoc` ON `appdb_group_partition_assoc`.`partition_id` = `appdb_partitions`.`partition_id`
   WHERE
     `appdb_group_partition_assoc`.`group_id` = gid
 ) as partition_names,
 (
   SELECT
 group_concat(appdb_roles.role_id SEPARATOR ', ')
   FROM
     `appdb_roles`
     JOIN
     `appdb_roles_groups_assoc` ON `appdb_roles_groups_assoc`.`role_id` = `appdb_roles`.`role_id`
   WHERE
     `appdb_roles_groups_assoc`.`group_id` = appdb_groups.group_id
 ) as role_ids,
 (
   SELECT
 group_concat(appdb_roles.name SEPARATOR ', ')
   FROM
     `appdb_roles`
     JOIN
     `appdb_roles_groups_assoc` ON `appdb_roles_groups_assoc`.`role_id` = `appdb_roles`.`role_id`
   WHERE
     `appdb_roles_groups_assoc`.`group_id` = appdb_groups.group_id
 ) as roles
FROM
 `appdb_groups`

However, this is what ends up getting compiled and executed:

Code:

SELECT
 `appdb_groups`.`group_id`,
 `appdb_groups`.`group_id` as `gid`,
 `appdb_groups`.`name`,
 `appdb_groups`.`description`,
 `appdb_groups`.`enabled`,
 (
   SELECT
     group_concat(appdb_accounts.username SEPARATOR ', ')
   FROM
     `appdb_accounts`
     JOIN
     `appdb_accounts_group_assoc` ON `appdb_accounts_group_assoc`.`account_id` = `appdb_accounts`.`account_id`
   WHERE
     --- HERE
     `appdb_accounts_group_assoc`.`group_id` = 'appdb_groups.group_id'
 ) as accounts,
 (
   SELECT
     group_concat(appdb_group_partition_assoc.partition_id SEPARATOR ', ')
   FROM
     `appdb_group_partition_assoc`
   WHERE
     --- HERE
     `appdb_group_partition_assoc`.`group_id` = 'gid'
 ) as partition_ids,
 (
   SELECT
     group_concat(distinct appdb_partitions.name SEPARATOR ', ')
   FROM
     `appdb_partitions`
   JOIN
     `appdb_group_partition_assoc` ON `appdb_group_partition_assoc`.`partition_id` = `appdb_partitions`.`partition_id`
   WHERE
     --- HERE
     `appdb_group_partition_assoc`.`group_id` = 'gid'
 ) as partition_names,
 (
   SELECT
     group_concat(appdb_roles.role_id SEPARATOR ', ')
   FROM
     `appdb_roles`
   JOIN
     `appdb_roles_groups_assoc` ON `appdb_roles_groups_assoc`.`role_id` = `appdb_roles`.`role_id`
   WHERE
     --- HERE
     `appdb_roles_groups_assoc`.`group_id` = 'appdb_groups.group_id'
 ) as role_ids,
 (
   SELECT
     group_concat(appdb_roles.name SEPARATOR ', ')
   FROM
     `appdb_roles`
   JOIN
     `appdb_roles_groups_assoc` ON `appdb_roles_groups_assoc`.`role_id` = `appdb_roles`.`role_id`
   WHERE
     --- HERE
     `appdb_roles_groups_assoc`.`group_id` = 'appdb_groups.group_id'
 ) as roles
 FROM
   `appdb_groups`

Look at the lines just below the ---HERE comment, you will notice that the value, which is supposed to be a table.column, is put into single quotes...

I tried to use backticks to remedy the situation, with the code used below:

PHP Code:

$accounts_select = self::$db
    ->select("group_concat({$this->_p('accounts')}.username SEPARATOR ',')")
    ->from('accounts')
    ->join("accounts_group_assoc", "accounts_group_assoc.account_id = accounts.account_id")
    ->where("{$this->_p('accounts_group_assoc')}.group_id","`{$this->_p('groups')}`.`group_id`")
    ->get_compiled_select();

$partition_ids_select = self::$db
    ->select("group_concat({$this->_p('group_partition_assoc')}.partition_id SEPARATOR ',')")
    ->from('group_partition_assoc')
    ->where("{$this->_p('group_partition_assoc')}.group_id","`gid`")
    ->get_compiled_select();

$partition_names_select = self::$db
    ->select("group_concat(distinct {$this->_p('partitions')}.name SEPARATOR ',')")
    ->from('partitions')
    ->join("group_partition_assoc", "group_partition_assoc.partition_id = partitions.partition_id")
    ->where("{$this->_p('group_partition_assoc')}.group_id","`gid`")
    ->get_compiled_select();

$role_ids_select = self::$db
    ->select("group_concat({$this->_p('roles')}.role_id SEPARATOR ',')")
    ->from('roles')
    ->join("roles_groups_assoc", "roles_groups_assoc.role_id = roles.role_id")
    ->where("{$this->_p('roles_groups_assoc')}.group_id","`{$this->_p('groups')}`.`group_id`")
    ->get_compiled_select();

$role_select = self::$db
    ->select("group_concat({$this->_p('roles')}.name SEPARATOR ',')")
    ->from('roles')
    ->join("roles_groups_assoc", "roles_groups_assoc.role_id = roles.role_id")
    ->where("{$this->_p('roles_groups_assoc')}.group_id","`{$this->_p('groups')}`.`group_id`")
    ->get_compiled_select();

self::$db
    ->select('groups.group_id, groups.group_id as gid, groups.name, groups.description, groups.enabled')
    ->select("({$accounts_select}) as accounts")
    ->select("({$partition_ids_select}) as partition_ids")
    ->select("({$partition_names_select}) as partition_names")
    ->select("({$role_ids_select}) as role_ids")
    ->select("({$role_select}) as roles");


if(isset($group))
    if( ! is_array($group))
        if(is_numeric($group))
            self::$db->where("{$this->_p('groups')}.group_id", $group);
        else
            self::$db->where("{$this->_p('groups')}.name", $group);
    else
        self::$db->where_in("{$this->_p('groups')}.group_id", $group);

$query = self::$db->get('groups'); 


But all that did was put backticks inside the single quotes, and resulting in the other query below:

Code:

SELECT
 `sassetdb_groups`.`group_id`,
 `sassetdb_groups`.`group_id` as `gid`,
 `sassetdb_groups`.`name`,
 `sassetdb_groups`.`description`,
 `sassetdb_groups`.`enabled`,
 (
   SELECT
     group_concat(sassetdb_accounts.username SEPARATOR ', ')
   FROM
     `sassetdb_accounts`
   JOIN
     `sassetdb_accounts_group_assoc` ON `sassetdb_accounts_group_assoc`.`account_id` = `sassetdb_accounts`.`account_id`
   WHERE
     --- HERE
     `sassetdb_accounts_group_assoc`.`group_id` = '`sassetdb_groups`.`group_id`'
 ) as accounts,
 (
   SELECT
     group_concat(sassetdb_group_partition_assoc.partition_id SEPARATOR ', ')
   FROM
     `sassetdb_group_partition_assoc`
   WHERE
     --- HERE
     `sassetdb_group_partition_assoc`.`group_id` = '`gid`'
 ) as partition_ids,
 (
   SELECT
     group_concat(distinct sassetdb_partitions.name SEPARATOR ', ')
   FROM
     `sassetdb_partitions`
   JOIN
     `sassetdb_group_partition_assoc` ON `sassetdb_group_partition_assoc`.`partition_id` = `sassetdb_partitions`.`partition_id`
   WHERE
     --- HERE
     `sassetdb_group_partition_assoc`.`group_id` = '`gid`'
 ) as partition_names,
 (
   SELECT
     group_concat(sassetdb_roles.role_id SEPARATOR ', ')
   FROM
     `sassetdb_roles`
   JOIN
     `sassetdb_roles_groups_assoc` ON `sassetdb_roles_groups_assoc`.`role_id` = `sassetdb_roles`.`role_id`
   WHERE
     --- HERE
     `sassetdb_roles_groups_assoc`.`group_id` = '`sassetdb_groups`.`group_id`'
 ) as role_ids,
 (
   SELECT
     group_concat(sassetdb_roles.name SEPARATOR ', ')
   FROM
     `sassetdb_roles`
   JOIN
     `sassetdb_roles_groups_assoc` ON `sassetdb_roles_groups_assoc`.`role_id` = `sassetdb_roles`.`role_id`
   WHERE
     --- HERE
     `sassetdb_roles_groups_assoc`.`group_id` = '`sassetdb_groups`.`group_id`'
 ) as roles
FROM
 `sassetdb_groups`

Is there a way to tell CI to NOT quote the value in the WHERE clause?

Thanks!

I poked around in the DB_query_builder.php and saw that the escape method is what was quoting the values, so changed that to FALSE and it seems to work fine.