Help with this SELECT statement

[eluser]jerry4christ[/eluser]
Please what am I doing wron here?

Code:

$this->db
   ->select("blog.*, blog_categories.title AS category_title, blog_categories.slug AS category_slug")
   ->select("SUM(IF(comments.is_active = 1,1,0)) total_comments")
   ->select('users.username, profiles.display_name')
   ->join('blog_categories', 'blog.category_id = blog_categories.id', 'left')
   ->join('comments', 'comments.entry_id = blog.id', 'left')
   ->join('profiles', 'profiles.user_id = blog.author_id', 'left')
   ->join('users', 'blog.author_id = users.id', 'left')
   ->order_by('created_on', 'DESC');

I get this error:

A Database Error Occurred

Error Number: 1064

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 '`total_comments`, `default_users`.`username`, `default_profiles`.`display_name` ' at line 1

SELECT `default_blog`.*, `default_blog_categories`.`title` AS `category_title`, `default_blog_categories`.`slug` AS `category_slug`, SUM(IF(comments.is_active = 1, `1 0))` `total_comments`, `default_users`.`username`, `default_profiles`.`display_name` FROM `default_blog` LEFT JOIN `default_blog_categories` ON `default_blog`.`category_id` = `default_blog_categories`.`id` LEFT JOIN `default_comments` ON `default_comments`.`entry_id` = `default_blog`.`id` LEFT JOIN `default_profiles` ON `default_profiles`.`user_id` = `default_blog`.`author_id` LEFT JOIN `default_users` ON `default_blog`.`author_id` = `default_users`.`id` WHERE `status` = 'live' AND `default_blog`.`created_on` <= 1413476587 ORDER BY `created_on` DESC LIMIT 100

[eluser]RobertSF[/eluser]
Hello... I am looking at the full SELECT statement you posted, not the Codeigniter $this->db code. You posted:

Code:

SELECT `default_blog`.*, `default_blog_categories`.`title` AS `category_title`,
`default_blog_categories`.`slug` AS `category_slug`, SUM(IF(comments.is_active = 1, `1 0))`
`total_comments`, `default_users`.`username`, `default_profiles`.`display_name` FROM `default_blog`
LEFT JOIN `default_blog_categories` ON `default_blog`.`category_id` = `default_blog_categories`.`id`
LEFT JOIN `default_comments` ON `default_comments`.`entry_id` = `default_blog`.`id` LEFT JOIN
`default_profiles` ON `default_profiles`.`user_id` = `default_blog`.`author_id` LEFT JOIN `default_users` ON
`default_blog`.`author_id` = `default_users`.`id` WHERE `status` = ‘live’ AND `default_blog`.`created_on` <=
1413476587 ORDER BY `created_on` DESC LIMIT 100

But the correct SELECT (at least free of syntax errors) is this:

Code:

SELECT `default_blog`.*, `default_blog_categories`.`title` AS `category_title`,
`default_blog_categories`.`slug` AS `category_slug`, SUM(IF(comments.is_active = 1, 1, 0)),
`total_comments`, `default_users`.`username`, `default_profiles`.`display_name` FROM `default_blog`
LEFT JOIN `default_blog_categories` ON `default_blog`.`category_id` = `default_blog_categories`.`id`
LEFT JOIN `default_comments` ON `default_comments`.`entry_id` = `default_blog`.`id` LEFT JOIN
`default_profiles` ON `default_profiles`.`user_id` = `default_blog`.`author_id` LEFT JOIN `default_users` ON
`default_blog`.`author_id` = `default_users`.`id` WHERE `status` = ‘live’ AND `default_blog`.`created_on` <=
1413476587 ORDER BY `created_on` DESC LIMIT 100

What is the difference? The difference is here

Code:

SUM(IF(comments.is_active = 1, `1 0))` `total_comments`,

That part should be

Code:

SUM(IF(comments.is_active = 1, 1, 0)), `total_comments`,

I hope this helps.

[eluser]jerry4christ[/eluser]
[quote author="RobertSF" date="1413522282"]Hello... I am looking at the full SELECT statement you posted, not the Codeigniter $this->db code. You posted:

Code:

SELECT `default_blog`.*, `default_blog_categories`.`title` AS `category_title`,
`default_blog_categories`.`slug` AS `category_slug`, SUM(IF(comments.is_active = 1, `1 0))`
`total_comments`, `default_users`.`username`, `default_profiles`.`display_name` FROM `default_blog`
LEFT JOIN `default_blog_categories` ON `default_blog`.`category_id` = `default_blog_categories`.`id`
LEFT JOIN `default_comments` ON `default_comments`.`entry_id` = `default_blog`.`id` LEFT JOIN
`default_profiles` ON `default_profiles`.`user_id` = `default_blog`.`author_id` LEFT JOIN `default_users` ON
`default_blog`.`author_id` = `default_users`.`id` WHERE `status` = ‘live’ AND `default_blog`.`created_on` <=
1413476587 ORDER BY `created_on` DESC LIMIT 100

But the correct SELECT (at least free of syntax errors) is this:

Code:

SELECT `default_blog`.*, `default_blog_categories`.`title` AS `category_title`,
`default_blog_categories`.`slug` AS `category_slug`, SUM(IF(comments.is_active = 1, 1, 0)),
`total_comments`, `default_users`.`username`, `default_profiles`.`display_name` FROM `default_blog`
LEFT JOIN `default_blog_categories` ON `default_blog`.`category_id` = `default_blog_categories`.`id`
LEFT JOIN `default_comments` ON `default_comments`.`entry_id` = `default_blog`.`id` LEFT JOIN
`default_profiles` ON `default_profiles`.`user_id` = `default_blog`.`author_id` LEFT JOIN `default_users` ON
`default_blog`.`author_id` = `default_users`.`id` WHERE `status` = ‘live’ AND `default_blog`.`created_on` <=
1413476587 ORDER BY `created_on` DESC LIMIT 100

What is the difference? The difference is here

Code:

SUM(IF(comments.is_active = 1, `1 0))` `total_comments`,

That part should be

Code:

SUM(IF(comments.is_active = 1, 1, 0)), `total_comments`,

I hope this helps.

[/quote]



Yes, you got it and see exactly what I see. But that's actually, my question, what is wrong with the codeigniter query? That's what I don't understand

[eluser]CroNiX[/eluser]
Query builder is for basic, simple queries. Think of all the thousands of possibilities they would have to consider to make it work for all possible sql combinations.

[eluser]RobertSF[/eluser]
[quote author="jerry4christ" date="1413527098"]Yes, you got it and see exactly what I see. But that's actually, my question, what is wrong with the codeigniter query? That's what I don't understand [/quote]

I don't think you're doing anything wrong. It looks like the Codeigniter select method in the active record object doesn't expect more than field names separated by commas. Here's the code:

Code:

68:      * Select
69:      *
70:      * Generates the SELECT portion of the query
71:      *
72:      * @param   string
73:      * @return  object
74:      */
75:     public function select($select = '*', $escape = NULL)
76:     {
77:         if (is_string($select))
78:         {
79:             $select = explode(',', $select);
80:         }
81:
82:         foreach ($select as $val)
83:         {
84:             $val = trim($val);
85:
86:             if ($val != '')
87:             {
88:                 $this->ar_select[] = $val;
89:                 $this->ar_no_escape[] = $escape;
90:
91:                 if ($this->ar_caching === TRUE)
92:                 {
93:                     $this->ar_cache_select[] = $val;
94:                     $this->ar_cache_exists[] = 'select';
95:                     $this->ar_cache_no_escape[] = $escape;
96:                 }
97:             }
98:         }
99:         return $this;
100:     }

In line 79, it explodes the select argument by commas, which works just fine when you do, for example:

Code:

$this->db->select('name, address, city, state, zip, phone, email')

but when you use

Code:

$this->db->select("SUM(IF(comments.is_active = 1,1,0)) total_comments")

looks like it doesn't expect the commas in the IF statement.

The Codeigniter documentation says that if you send the select method an optional second parameter with the value FALSE, that the method won't then try to protect your field names with backticks. That may work. There is also a select_sum method that may do the trick. If not, just write the query out in full.

[eluser]rufnex[/eluser]
Look here https://ellislab.com/forums/viewthread/246771/