ActiveRecord: Using mysql functions within select?

[eluser]gRoberts[/eluser]
Hi all,

I need to use functions such as ifnull within my select query but due to the fact that I have a database prefix, its failing.

i.e:

Code:

$this->db->select('sr.*, ifnull(sgr.RoleID, -1) != -1 as ShowActive', false);

produces an error:

Code:

SELECT sr.*, dev_ifnull(sgr.RoleID, -1) != -1 as ShowActive FROM

For some reason, its prefixing the functions with my database prefix.

Any idea's on how to get around this?

Cheers

Gav

[eluser]Thorpe Obazee[/eluser]

Code:

$this->db->select('(SELECT DATE_FORMAT(posts.date_add, "%Y/%m/%d") FROM '.$this->db->dbprefix('posts').') as posts_date_add', FALSE);

here's a sample of how you can use db prefixes and mysql functions.

[eluser]gRoberts[/eluser]
The DBPrefix is being automatically added. I would have to disable this feature and manually add the prefix to all of my queries... Not very good.

My issue is that its adding the prefix to something that isn't a table.

Thanks anyway

[eluser]simshaun[/eluser]
Sounds like this may need moved to the bug reports if it hasnt been already.

[eluser]gRoberts[/eluser]
Is it as simple as asking a moderator to move this into the bug forum?

[eluser]Thorpe Obazee[/eluser]
doesn't this work?

Code:

$this->db->select('sr.*')
$this->db->select('(SELECT IFNULL(RoleID, -1) != -1 FROM '.$this->db->dbprefix('sgr').') as ShowActive', FALSE)

[eluser]gRoberts[/eluser]
That it does:

Code:

$this->db->select('sr.*');
$this->db->select('(case ifnull(sgr.RoleID, -1) when -1 then 0 else 1 end) as ShowActive', false);

Thank you!

I'll update the thread in the bug forum.

[eluser]Thorpe Obazee[/eluser]
No problem