CodeIgniter ActiveRecord keeps getting worse

[eluser]OverZealous[/eluser]
I'm worried about upgrading to CI 1.7.1 due to the changes with the ActiveRecord class.

It has been bad enough trying to get _protect_identifiers to not break everything, but now the order_by method splits the input by commas blindly! I understand on the from method, because tables shouldn't have functions, but order_by?

A simple example: I have two numeric fields that may be NULL, cost and revenue. Now, if I want to order by profit (which is the difference between them), I have to use coalesce (a standard SQL function) to convert NULLs to 0.

My order by looks like this:

Code:

$this->db->order_by('(coalesce(revenue, 0) - coalesce(cost, 0))');

This worked on 1.7.0 after I included the updated _protect_identifiers that stopped being so darn helpful if the field included open parentheses.

Now, however, order_by is going to convert this completely valid line into this:

Code:

// ends up something like:
ORDER BY (coalesce(revenue, 0) - coalesce(cost, "0))"

If ActiveRecord's order_by MUST process these items, it needs to have the same strategy as _protect_identifiers. If the field contains open parentheses, just don't split it.

Why can't the CI team just let some things be?

Frustrated,

[eluser]OverZealous[/eluser]
Hmmm,

I apologize for my "outburst". Apparently, the code above does not break. I will keep testing.

However, part of my complaint still stands - that the splitting is done blindly, and then the strings trimmed, and the result merged. If the field had a comma and multiple spaces, they would get changed.

[eluser]GKenny[/eluser]
Just to revive this thread. I came accross a similar problem.

I have a query that uses:

Code:

$this->db->order_by('FIELD(e.somenumber, 4,1,2,3), t, u.somedate');

And of course, the new AR order_by function is blindly split the part using a comma! So it ends up being messed up.

I did have to hack the DB_active_rec.php file and replace the order_by function with this one:

Code:

function order_by($orderby, $direction = '')
    {
        if (strtolower($direction) == 'random')
        {
            $orderby = ''; // Random results want or don't need a field name
            $direction = $this->_random_keyword;
        }
        elseif (trim($direction) != '')
        {
            $direction = (in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE)) ? ' '.$direction : ' ASC';
        }
        
        $orderby_statement = $this->_protect_identifiers($orderby, TRUE).$direction;
        
        $this->ar_orderby[] = $orderby_statement;
        if ($this->ar_caching === TRUE)
        {
            $this->ar_cache_orderby[] = $orderby_statement;
        }

        return $this;
    }

Note: This is one from CI 1.6.1

Up it helps some people!

[eluser]ShawnMcCool[/eluser]
Great post, GKenny.