DB Active Record MAX, MIN, etc. fix - I think...

[eluser]gregmax17[/eluser]
I was modifying the PyroCMS today to add a feature to the installer. The feature I was adding was related to the table prefix in the Database Active Record, this wasn't thought of apparently before hand (silly Phil).

Anyways, I implemented the feature but then the queries stopped working. After some debugging I found that this query was causing the error:

Code:

$this->db->select('comments.*');
        $this->db->select('IF(comments.user_id > 0, IF(u.last_name = "", u.first_name, CONCAT(u.first_name, " ", u.last_name)), comments.name) as name');
        $this->db->select('IF(comments.user_id > 0, u.email, comments.email) as email');
        $this->db->join('users u', 'comments.user_id = u.id', 'left');

So I did more debugging in CI's DB Driver to find the cause and fixed it. In the DB Driver file (on line 1253 maybe?), it just returns the $item.$alias if the $item contains the '(' character. (look for a comment by Rick if that helps)

Here is what I did to get it to work:

*begin edit*
Change this line of code:

Code:

if (strpos($item, '(') !== FALSE)
{
    return $item.$alias;
}

To be this instead:

Code:

if (strpos($item, '(') !== FALSE && strpos($item, '.') === FALSE)
{
    return $item.$alias;
}

*end edit*

Then a few lines below you should see this:

Code:

if (strpos($item, '.') !== FALSE)
        {
            $parts    = explode('.', $item);
   // etc...

Change it to be:

Code:

if (strpos($item, '.') !== FALSE)
        {
            $parts    = explode('.', $item);
            
            // check for the MAX, MIN, etc.
            if (strpos($parts[0], '(') !== FALSE)
            {
                $action = substr($parts[0], 0, strpos($parts[0], '(') + 1);
                $parts[0] = substr($parts[0], strpos($parts[0], '(') + 1);
            }
            else
            {
                $action = '';
            }

Then finally, after these lines of code (about ~60 lines down):

Code:

if ($protect_identifiers === TRUE)
            {
                $item = $this->_escape_identifiers($item);
            }
                        return $item.$alias;

Change it be this instead:

Code:

if ($protect_identifiers === TRUE)
            {
                $item = $this->_escape_identifiers($item);
            }
                        return $action.$item.$alias;

I appreciate any feedback on this. I am only going to be debugging from what I work with on PyroCMS (for the moment). I hope it helps someone else out there.

[eluser]Phil Sturgeon[/eluser]
I'm watching... always watching. :coolmad:

[eluser]gregmax17[/eluser]
Oops, I found an error in my script already...

In the DB driver class, where Rick's comment is, I suggest to remove that IF statement or comment the return. But in fact, it should be this:

Code:

if (strpos($item, '(') !== FALSE && strpos($item, '.') === FALSE)
{
    return $item.$alias;
}

Basically I am checking if we are going to add a table prefix to the item or not.

Again, I am just testing this on PyroCMS, but I encourage everyone to use it to bullet proof this method.

@phil
I finally forked your awesome PyroCMS and using these changes, got it to install the table prefix's without any errors