-
jLinux
The Linux Dude
-
Posts: 157
Threads: 35
Joined: Jun 2015
Reputation:
2
09-19-2015, 03:07 PM
(This post was last modified: 09-19-2015, 03:09 PM by jLinux.)
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!
|