Crosstab Query Not Working With ActiveRecord

[eluser]Einspruch[/eluser]
Question: this query works from the command line, so the syntax works. But I can not translate it into ActiveRecord.

This is the MySQL syntax:

Code:

SELECT Partners.Partner, Titles.Title,
SUM(IF(Digital_Results.Month = "2008-01-01 00:00:00", Digital_Results.Royalty,0)),
SUM(IF(Digital_Results.Month = "2008-02-01 00:00:00", Digital_Results.Royalty,0)),
SUM(IF(Digital_Results.Month = "2008-03-01 00:00:00", Digital_Results.Royalty,0)),
FROM ((((Digital_Results
JOIN Partners ON Digital_Results.Partner_ID = Partners.ID)
JOIN Titles ON Digital_Results.Title_ID = Titles.ID)
GROUP BY Partners.Partner, Titles.Title;

And this is the ActiveRecord syntax:

Code:

$this->db->select('Partners.Partner, Titles.Title, SUM(IF(Digital_Results.Month = "2008-01-01 00:00:00", Digital_Results.Royalty,0)), SUM(IF(Digital_Results.Month = "2008-02-01 00:00:00", Digital_Results.Royalty,0)), SUM(IF(Digital_Results.Month = "2008-03-01 00:00:00", Digital_Results.Royalty,0))');
$this->db->from('Digital_Results');
$this->db->join('Titles', 'Titles.ID = Digital_Results.Title_ID');
$this->db->join('Partners', 'Partners.ID = Digital_Results.Partner_ID');
$this->db->group_by("Titles.Title, Partners.Partner");
$this->db->order_by("Titles.Title", "ASC");
$this->db->order_by("Month", "ASC");
$this->db->order_by("Partners.Partner", "ASC");
$data['query'] = $this->db->get();

And this is what I end up with...

Code:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' SUM(IF(Digital_Results.Month = "2008-02-01 00:00:00", `Digital_Results`.`Royalt' at line 1

Any and all help is appreciated!!! :-)

[eluser]davidbehler[/eluser]
try this

Code:

$this->db->select('Partners.Partner, Titles.Title');
$this->db->select('SUM(IF(Digital_Results.Month = "2008-01-01 00:00:00", Digital_Results.Royalty,0))', FALSE);
$this->db->select('SUM(IF(Digital_Results.Month = "2008-03-01 00:00:00", Digital_Results.Royalty,0))', FALSE);
$this->db->select('SUM(IF(Digital_Results.Month = "2008-02-01 00:00:00", Digital_Results.Royalty,0))', FALSE);
$this->db->from('Digital_Results');
$this->db->join('Titles', 'Titles.ID = Digital_Results.Title_ID');
$this->db->join('Partners', 'Partners.ID = Digital_Results.Partner_ID');
$this->db->group_by("Titles.Title, Partners.Partner");
$this->db->order_by("Titles.Title", "ASC");
$this->db->order_by("Month", "ASC");
$this->db->order_by("Partners.Partner", "ASC");
$data['query'] = $this->db->get();

[eluser]TheFuzzy0ne[/eluser]
The problem is that the AR class is escaping your select query and it's breaking it due to the functions. By passing FALSE as the second parameter to the select method, the fields are not escaped. It's documented in the [url="http://ellislab.com/codeigniter/user-guide/database/active_record.html"]user guide[/url].

[eluser]Einspruch[/eluser]
That did the trick. Thanks!