Problem with Active Record class select method

[eluser]maikens55[/eluser]
I am trying to build a select portion of a query using CI's Active Record class with the select method. However, I have found if I try to include more than one CONCAT_WS statement in the select, it breaks.

essentially, I am doing:

Code:

$select = '`estimates`.`estimate_number`, `customers`.`name`, `estimates`.`estimate_type`, CONCAT_WS(", ", `estimates`.`meeting_date`, `estimates`.`meeting_complete`, `estimates`.`delivery_complete`, 'meeting'), CONCAT_WS(", ", `estimates`.`delivery_date`, `estimates`.`meeting_complete`, `estimates`.`delivery_complete`, 'delivery'), `estimates`.`status`, `estimates`.`price`'

$this->db->select($select, false);

I get the following error when the query is executed:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), `estimates`.`status`, `estimates`.`price` FROM (`estimates`) LEFT OUTER JOIN ' at line 1

SELECT `estimates`.`id`, `estimates`.`estimate_number`, `customers`.`name`, `estimates`.`estimate_type`, CONCAT_WS(", ", `estimates`.`meeting_date`, `estimates`.`meeting_complete`, `estimates`.`delivery_complete`, meeting), `estimates`.`delivery_date`, delivery), `estimates`.`status`, `estimates`.`price` FROM (`estimates`) LEFT OUTER JOIN `customers` ON `estimates`.`customer_id` = `customers`.`id` WHERE (estimates.user_id = 140 AND `estimates`.`archived` = 0) LIMIT 10

At this point, it is not feasible for me to abandon using Active Record, because I have spent a lot of time building a search tool using it. Can anyone tell me if this is a bug parsing the string passed as first param to select(), or if I screwed it up somehow?

I am using CI version 2.1.0

Thanks

[eluser]CroNiX[/eluser]
->select(all the normal stuff)
->select(your concat, FALSE)
->select(another concat, FALSE)

[eluser]maikens55[/eluser]
Hi, I probably should have mentioned I tried it that way first, then concatenated the portions together in a single select call to see if it would yield a better result. I still end up with the same result.

[eluser]maikens55[/eluser]
I have looked into the DB_active_rec.php file and determined the problem.

The problem is caused by using cache. During the _compile_select() call, called from get(), it calls the _merge_cache function. Since the select statement is simply exploded by comma delimiters, the CONCAT_WS statement is broken up into 6 array elements. It looks like this:

array (size=32)
0 => string 'estimates.id' (length=12)
1 => string 'estimates.estimate_number' (length=25)
2 => string 'customers.name' (length=14)
3 => string 'estimates.estimate_type' (length=23)
4 => string 'CONCAT_WS("' (length=11)
5 => string '"' (length=1)
6 => string '`estimates`.`meeting_date`' (length=26)
7 => string '`estimates`.`meeting_complete`' (length=30)
8 => string '`estimates`.`delivery_complete`' (length=31)
9 => string '"meeting")' (length=10)
10 => string 'CONCAT_WS("' (length=11)
11 => string '"' (length=1)
12 => string '`estimates`.`delivery_date`' (length=27)
13 => string '`estimates`.`meeting_complete`' (length=30)
14 => string '`estimates`.`delivery_complete`' (length=31)
15 => string '"delivery")' (length=11)
16 => string 'estimates.status' (length=16)
17 => string 'estimates.price' (length=15)
18 => string 'estimates.id' (length=12)
19 => string 'estimates.estimate_number' (length=25)
20 => string 'customers.name' (length=14)
21 => string 'estimates.estimate_type' (length=23)
22 => string 'CONCAT_WS("' (length=11)
23 => string '"' (length=1)
24 => string '`estimates`.`meeting_date`' (length=26)
25 => string '`estimates`.`meeting_complete`' (length=30)
26 => string '`estimates`.`delivery_complete`' (length=31)
27 => string '"meeting")' (length=10)
28 => string '`estimates`.`delivery_date`' (length=27)
29 => string '"delivery")' (length=11)
30 => string 'estimates.status' (length=16)
31 => string 'estimates.price' (length=15)

However, on lines 1942 - 1952, it does this:

Code:

foreach ($this->ar_cache_exists as $val)
  {
   $ar_variable = 'ar_'.$val;
   $ar_cache_var = 'ar_cache_'.$val;

   if (count($this->$ar_cache_var) == 0)
   {
    continue;
   }

   $this->$ar_variable = array_unique(array_merge($this->$ar_cache_var, $this->$ar_variable));
  }

Because it filters out the array using array_unique, and because more than half of the pieces of each CONCAT_WS are common due to the syntax of the function and the similarity of the parameters. most of the sections of the second call are removed from the array.

I am not certain how to fix this elegantly while not introducing any bugs. Any suggestions?

[eluser]maikens55[/eluser]
For anyone interested (although this issue may have been resolved in a more recent version of this class):

I simply parsed the select statement for brackets, and replaced any commas inside those brackets with a placeholder. Then when it performs the explode, it doesn't mangle the statement. I replace the commas before putting the statement into the cache, and the query is properly formed.

[eluser]Unknown[/eluser]
I got the same problem and solved it suddenly.

I uploaded the file that is modified to solve the problem of array_unique() in _merge_cache().

I'd like it is useful to someone who got the same issue.