CI 1.6: DB_active_rec.php -> function select()

[eluser]slackero[/eluser]
I have upgraded my in-dev application to current SVN of CI 1.6 - hey and it drives me nuts because my whole application stopped to work.

The new param $protect_identifiers of function select() in DB_active_rec.php should be set FALSE by default and not TRUE!

"Old" selects like this one

Code:

$this->db->select("
    TRIM(
        TRAILING '-' FROM SUBSTRING_INDEX(article_cat, '|', 1)
    ) AS shop_cat,
    COUNT(article_cat) AS cat_count" );

will fail because the "optimized" function will result in this:

Code:

...TRIM( `TRAILING` '-' `FROM` SUBSTRING_INDEX(`article_cat`, '|', `1`) )...

[eluser]Derek Allard[/eluser]
Yes, this is one problem introduced, and I'm keenly aware of it. You'll need to add a FALSE parameter into there

Code:

$this->select->db('statement', FALSE);

to prevent the identifier escaping. Believe me that I understand this problem, as my own projects are immediately impacted, but fortunately in CI we've been saving all our database calls in models, and it is only a minute or two of copy/paste.

Sorry for any frustration here slackero, but it was made for the betterment of the framework and community.

[eluser]slackero[/eluser]
Thanks for reply Derek,

Sorry for any frustration here slackero, but it was made for the betterment of the framework and community.

No problem ;-). Lot of work, but it's done - and working. For all others being not able or willing to change whole code it's simple to change param's default to FALSE...

Thanks again for coming 1.6 - it is working very well it seems

[eluser]matt2012[/eluser]
Im having this problem where I want to write a subquery in the from for a subquery join

I get the `select` `from` issue but setting select to false does not solve the problem.

Code:

$this->db->from("table1 LEFT JOIN
        ( select col1, col2
            from table2
            where  id = 13
        ) as table3
        ON table1.col1 = table3.col1");

[eluser]Sam Dark[/eluser]
Sam here:

Code:

$this->db->select('t.tag, COUNT(pt.id) as qty');
$this->db->from('blog_tags t');        
$this->db->join('blog_posts_tags pt', 'pt.tag_id = t.id', 'inner');        
$this->db->groupby('t.tag');        
$query = $this->db->get();

It's from Blaze.

[eluser]B3ll4triX[/eluser]
I have same problem with my application, this query doesn't run in CI v1.7, but good in v1.6.3.

Code:

$data = array();
$this->db->select('gallery.*,
(SELECT count(gallery_images.image_id) FROM gallery_images WHERE gallery_images.gallery_id = gallery.gallery_id) as count');
$this->db->from('gallery');
$this->db->limit($num, $offset);
$this->db->order_by('priority', 'desc');
$query = $this->db->get();
if($query->num_rows() > 0){
    foreach ($query->result() as $row){
        $data[] = $row;
    }
}
$query->free_result();
return $data;

How to fix this problem, because i have more code like that.

-th4nks b4-

[eluser]Derek Allard[/eluser]
B3ll4triX, did this advice help? Its from a few posts above.

[eluser]enrico.simonetti[/eluser]
Hi there,

I migrated as well from 1.6.* to 1.7 and encountered the same problem described above, selecting:

$this->db->select('something.*, somethingelse.onefield');

using a join.

I've added the FALSE as second parameter and it works fine, but probably I shouldn't be forced to escape the filtering. Probably the .* should be handled by _protect_identifiers, it is a common thing to do... ?

Anyway apart from this issue, if I select a single field, without second parameter = FALSE, and i add an ordering on the query:

$this->db->orderby('','random');
or also

$this->db->orderby('sometable.somefield','random');

a "`" get added between BY and RAND() ("ORDER BY ` RAND() LIMIT 1"), causing the query to fail...


Thanks,

Enrico

[eluser]Max Nagaychenko[/eluser]
[quote author="enrico.simonetti" date="1225620693"]
....
$this->db->order_by('','random');
or also

$this->db->order_by('sometable.somefield','random');

a "`" get added between BY and RAND() ("ORDER BY ` RAND() LIMIT 1"), causing the query to fail...


Thanks,

Enrico[/quote]

See http://codeigniter.com/bug_tracker/bug/b..._order_by/

[eluser]Dan Herd[/eluser]
Just posting this as a matter of record in case anyone else encounters a similar problem.

I am using a bit of custom MySQL to calculate the median from a list of values generated by a sub-query. This worked perfectly well in 1.6, but was broken in 1.7

Code:

$this->dbs['peer']->select(
"`specialty`, `chapter`, `hrg`, `day`, SUM(`sumnums`) AS `sumnums`, SUM(`sumdenoms`) AS `sumdenoms`,
((SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(`rate` ORDER BY `rate`), ',',CEILING(COUNT(*) * 0.5)), ',', -1) + SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(`rate` ORDER BY `rate`), ',',-CEILING(COUNT(*) * 0.5)), ',', 1)) / 2) AS `median`,
STDDEV_SAMP(`rate`) AS sd, COUNT(*) AS `numpcts` FROM ({$subquery}) AS `data`"
,false
);

What's the problem? Well CodeIgniter now adds a space after every comma it encounters in a query, even if that comma is quoted in a string, as is the case here. The GROUP_CONCAT function separates each result with a comma by default, so that extra space screws it up. It fails by simply giving an incorrect result, rather than throwing an error as well.

The solution is to change the query as follows:

Code:

$this->dbs['peer']->select(
"`specialty`, `chapter`, `hrg`, `day`, SUM(`sumnums`) AS `sumnums`, SUM(`sumdenoms`) AS `sumdenoms`,
((SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(`rate` ORDER BY `rate` SEPARATOR '|'), '|',CEILING(COUNT(*) * 0.5)), '|', -1) + SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(`rate` ORDER BY `rate` SEPARATOR '|'), '|',-CEILING(COUNT(*) * 0.5)), '|', 1)) / 2) AS `median`,
STDDEV_SAMP(`rate`) AS sd, COUNT(*) AS `numpcts` FROM ({$subquery}) AS `data`"
,false
);

Now, with the pipe being used instead of a single comma, CI doesn't screw up the query and we have a correct median again.

I wasted an entire afternoon discovering this by the way. After being the official CI evangelist in the company I work for, this is quite a setback in my quest for us to adopt this framework to be honest...

EDIT: I see there is a related bug already filed:

http://codeigniter.com/bug_tracker/bug/6663/

I hope this is fixed soon.