CodeIgniter Forums
Odd behaviour with Active Record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Odd behaviour with Active Record (/showthread.php?tid=281)

Odd behaviour with Active Record - sintakonte - 11-18-2014

Hey guys,

for your information i'm using CI 3:

Can anyone of you reproduce what i've experienced;
I've a compound select query like:

PHP Code:
$query = $this->db

->select("ur.*, ca.name, GROUP_CONCAT(ug.id SEPARATOR ',') AS usergrouplist",false)... 

Now, my problem is the generated Query
Code:
SELECT ur.*, ca.name, GROUP_CONCAT(ug.id SEPARATOR ', ') AS usergrouplist

There is a space after the comma
Am i overlooking something or is there a bug in CI ?

RE: Odd behaviour with Active Record - sv3tli0 - 11-18-2014

For too advanced queries I can suggest you to input normal query inline..

Code:
$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick'));

Check here http://www.codeigniter.com/user_guide/database/queries.html

RE: Odd behaviour with Active Record - sintakonte - 11-18-2014

Yeah you are right, i can do that, but i'm to lazy to write full queries Wink
On the other hand, is this suggestion a recommendation or just a workaround for my problem because in my case the whole query looks like

PHP Code:
$query = $this->db
    ->select("ur.*, ca.name, GROUP_CONCAT(ug.id SEPARATOR ',') AS usergrouplist",false)
    ->from("UserRights AS ur")
    ->join("Categories AS ca","ca.id = ur.Item_id","inner")
    ->join("UserGroups_UserRights AS uu", "uu.UserRights_id = ur.id","inner")
    ->join("UserGroups AS ug","ug.id = uu.UserGroups_id","inner")
    ->where("ca.Modules_id", $module_id)
    ->where("ur.typ","Category")
    ->where("ur.name","read")
    ->where("ca.active",1)
    ->group_by("ur.id")
    ->order_by("ca.name")
    ->get(); 

And i really don't want to set aside the benefits of the Query Builder...

RE: Odd behaviour with Active Record - sv3tli0 - 11-18-2014

(11-18-2014, 03:31 AM)sintakonte Wrote: Yeah you are right, i can do that, but i'm to lazy to write full queries Wink
On the other hand, is this suggestion a recommendation or just a workaround for my problem because in my case the whole query looks like


And i really don't want to set aside the benefits of the Query Builder...

You know that Query Builder is just a tool to generate correct query. So at the end you always get 1 line query.. Smile

At least from my experience if you have Big and Complicated query, its harder to use a Query Builder than to write it plain. 
So in some cases the better choice is a plain query..

In other cases where you have dynamic dependences QB is the best choice.

RE: Odd behaviour with Active Record - Narf - 11-18-2014

https://github.com/bcit-ci/CodeIgniter/issues/634

RE: Odd behaviour with Active Record - ivantcholakov - 11-18-2014

@sintakonte

For this particular case you may try a temporary workaround about the bug for now. Use $this->db->get_compiled_select() method and see the generated $sql. Then, by using string search and replace on the $sql correct ', ' to ','. And then use $this->db->query($sql).

RE: Odd behaviour with Active Record - Rufnex - 11-18-2014

This problem results out of a core function inside the
Code:
DB_query_builder.php
locatet under /system/core/database

Here you can search for the protected function
Code:
protected function _compile_select
on line 2259 (on CI 3.x).

Code:
$sql .= implode(', ', $this->qb_select);

So here must be changed the space for the implode function. But - correct me if iam wrong - its not possible to extend the Database Classes without a deeper hack.

RE: Odd behaviour with Active Record - sintakonte - 11-19-2014

thx for your answers guys

PHP Code:
$query = $this->db
    ->select(array("ur.*, ca.name, GROUP_CONCAT(ug.id SEPARATOR ',') AS usergrouplist"),false)... 

i tested the array solution and it works like a charm