Crosstab Query Not Working With ActiveRecord

Crosstab Query Not Working With ActiveRecord - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Crosstab Query Not Working With ActiveRecord (/showthread.php?tid=16475)

Crosstab Query Not Working With ActiveRecord - El Forum - 03-08-2009

[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!!! :-)

Crosstab Query Not Working With ActiveRecord - El Forum - 03-08-2009

[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();

Crosstab Query Not Working With ActiveRecord - El Forum - 03-08-2009

[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].

Crosstab Query Not Working With ActiveRecord - El Forum - 03-27-2009

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