Blog module group by issue 1055 error

Comments

• admin

  August 2018

  Can you run $this->debug_query() after it executes the query to output the raw SQL statement?
• admin

  August 2018

  Actually... that won't work if it's erring before that.
• admin

  August 2018

  It looks like it has something to do with the MySQL setting of "STRICT_TRANS_TABLES" (new one for me). I've pushed that fix to the blog module.
• Gumster

  August 2018 edited August 2018

  I was getting this specifically:
  Error Code: 1055. Expression #26 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'display_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

  The expression number varied in each model instance .

  It was new to me too - a new Cpanel / MySQL configuration default!?
• marcus_at_localhost

  December 2018

  I came across the same error (MySQL 5.7)

  Error in query (1055): Expression #16 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'temp.posts_count' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

  with this query:

  SELECT 
  authors.*, 
  CONCAT(first_name, " ", last_name) as name, 
  fuel_users.first_name, 
  fuel_users.last_name, 
  fuel_users.email, 
  fuel_users.user_name, 
  fuel_users.active as users_active, 
  `posts_count` FROM `authors` LEFT JOIN `fuel_users` ON `fuel_users`.`id` = `authors`.`fuel_user_id` 
  LEFT JOIN (
      SELECT COUNT(*) AS posts_count, 
      news.author_id 
      FROM news GROUP BY news.author_id
  ) AS temp ON `temp`.`author_id`= `fuel_users`.`id` WHERE `authors`.`fuel_user_id` = '1' 
  GROUP BY `fuel_users`.`id`, `authors`.`id` LIMIT 1
  

  Which originated from this piece of code in my Authors_model (copied from blog module I believe)

      function _common_query($display_unpublished_if_logged_in = NULL)
      {
          parent::_common_query($display_unpublished_if_logged_in);
          $this->db->select('authors.*, CONCAT(first_name, " ", last_name) as name, '.$this->_tables['fuel_users'].'.first_name, '.$this->_tables['fuel_users'].'.last_name, '.$this->_tables['fuel_users'].'.email, '.$this->_tables['fuel_users'].'.user_name, '.$this->_tables['fuel_users'].'.active as users_active', FALSE);
          $this->db->select('posts_count'); // for temp table to get posts count
          $this->db->group_by($this->_tables['fuel_users'].'.id, authors.id');
      }
  
      function _common_joins()
      {
  
          $this->db->join($this->_tables['fuel_users'], $this->_tables['fuel_users'].'.id = authors.fuel_user_id', 'left');
          $this->db->join('(SELECT COUNT(*) AS posts_count, news.author_id FROM news GROUP BY news.author_id) AS temp', 'temp.author_id= fuel_users.id', 'left'); 
      }
  

  The solution, and this StackOverflow answer helped me to understand the problem, was adding temp.posts_count to the group by clause

  $this->db->group_by($this->_tables['fuel_users'].'.id, authors.id, temp.posts_count');
  

  I'll send a pull request on github.
• admin

  December 2018

  There was a fix for that posted in August I believe:
  https://github.com/daylightstudio/FUEL-CMS-Blog-Module/commit/d4d0e673285939321694a8e29bcd55253b913411
• marcus_at_localhost

  December 2018

  yes, but I got this error:

  Error Number: 1055
  
  Expression #16 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'temp.posts_count' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  
  SELECT authors.*, CONCAT(first_name, " ", last_name) as name, fuel_users.first_name, fuel_users.last_name, fuel_users.email, fuel_users.user_name, fuel_users.active as users_active, `posts_count` FROM `authors` LEFT JOIN `fuel_users` ON `fuel_users`.`id` = `authors`.`fuel_user_id` LEFT JOIN (SELECT COUNT(*) AS posts_count, news.author_id FROM news GROUP BY news.author_id) AS temp ON `temp`.`author_id`= `fuel_users`.`id` WHERE `authors`.`fuel_user_id` = '1' GROUP BY `fuel_users`.`id`, `authors`.`id` LIMIT 1
  
  Filename: /fuel/modules/fuel/core/MY_Model.php
  
  Line Number: 481
  

  and adding , temp.posts_count solved it. And the error message is pointing out that "nonaggregated column 'temp.posts_count'" is the issue. I only had this problem on the live server where MySQL was configured more strict. Maybe it helps if someone else is coming across this.

  Cheers.