[Active Record] Select AS Backticks problem

[eluser]Azhari Harahap[/eluser]
Hi

i'm using active record to make my SQL query works with all database egnine, but i have a problem on SELECT AS with PostgreSQL.

For example i have this:

Code:

$this->db->select('Name as GroupName')
$this->db->from('pbk');

In MySQL it'll create:

Code:

SELECT `Name` as GroupName FROM (`pbk`)

In PostgreSQL:

Code:

SELECT "Name" as GroupName FROM "pbk"

But, the result will be different, on MySQL it stay as GroupName, but on PostgreSQL it become groupname (It's because PostgreSQL case-sensitive behavior).

The solution is to add backticks (based on driver) to as/alias field name.
On MySQL:

Code:

SELECT `Name` as `GroupName` FROM (`pbk`)

On PostgreSQL:

Code:

SELECT "Name" as "GroupName" FROM "pbk"

Any way to do this?

It will be nice if there is:

Code:

$this->db->select_as('Name', 'GroupName');

[eluser]vikascoollives[/eluser]
You can use :

$this->db->select('name', 'Groupname');
$query = $this->db->get('members');

// Produces: SELECT (name) as GroupnameFROM members in codeigniter

[eluser]Azhari Harahap[/eluser]
Hi

[quote author="vikascoollives" date="1299588504"]You can use :
$this->db->select('name', 'Groupname');
$query = $this->db->get('members');

// Produces: SELECT (name) as GroupnameFROM members in codeigniter[/quote]

This not work, as tested on 1.7.2.
I've check through the user guide and source code, select method only accept two parameter.

Code:

function select($select = '*', $escape = NULL)

The second parameter only accept boolean value (TRUE or FALSE).

[eluser]InsiteFX[/eluser]
Try using double qoutes and not single qoutes.

Active Record automatically escapes your queries.

InsiteFX

[eluser]Azhari Harahap[/eluser]
[quote author="InsiteFX" date="1299603199"]Try using double qoutes and not single qoutes.

Active Record automatically escapes your queries.

InsiteFX[/quote]

Do you mean like this?

Code:

$this->db->select("Name as GroupName")

This works the same as single quotes, i got same result.

Code:

SELECT `Name` as GroupName FROM (`pbk`)

[eluser]Azhari Harahap[/eluser]
Finally get it working with little addition to the AR class.

Code:

// --------------------------------------------------------------------

    /**
     * Select AS
     *
     * Generates the SELECT(field AS) portion of the query
     *
     * @access    public
     * @param    string
     * @return    object
     */
    function select_as($select = '', $alias = '')
    {        
        if ( ! is_string($select) OR $select == '')
        {
            $this->display_error('db_invalid_query');
        }

        if ( ! is_string($alias) OR $alias == '')
        {
            $this->display_error('db_invalid_query');
        }        

        $sql = $this->_protect_identifiers(trim($select)).' AS '.$this->_protect_identifiers(trim($alias));

        $this->ar_select[] = $sql;
        
        if ($this->ar_caching === TRUE)
        {
            $this->ar_cache_select[] = $sql;
            $this->ar_cache_exists[] = 'select';
        }        

        return $this;
    }

And then you can do this:

Code:

$this->db->select_as('Name', 'GroupName');

It will produce:

Code:

SELECT `Name` as `GroupName` FROM (`pbk`)