GriefCode Posted December 12, 2016 Share Posted December 12, 2016 I'm having some trouble in grouping by, this is my code: $table = new \IPS\Helpers\Table\Db('reportarchive_data', \IPS\Http\Url::internal('app=reportarchive&module=base&controller=archive'), null, 'reportarchive_data.id'); $table->langPrefix = 'archive_view_'; $table->joins = array( array( 'select' => 'reportarchive_history.author_id lastAuthor, reportarchive_data.id as reportId, reportarchive_history.date as lastModified', 'from' => 'reportarchive_history', 'where' => array('reportarchive_data.id=reportarchive_history.reportarchive_data_id') ) ); I'm probably getting this error: SELECT * FROM `reportarchive_data` LEFT JOIN `reportarchive_history` ON reportarchive_data.id=reportarchive_history.reportarchive_data_id GROUP BY `reportarchive_data`.`id` LIMIT 0,1 IPS\Db\Exception: Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'forum.reportarchive_history.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (1055) Which is caused by IPS setting the sql mode in dev. How can I fix my group by? Thanks Link to comment Share on other sites More sharing options...
bfarber Posted December 13, 2016 Share Posted December 13, 2016 Group by's can be difficult depending upon the SQL mode, and we enforce a strict SQL mode while developing to help catch issues. The general recommendation is to adjust the end query like so SELECT * FROM table WHERE id IN(SELECT id FROM table WHERE x=y GROUP BY id) That would mean not passing a group by parameter to the table helper, but adjusting the WHERE clause passed to the table helper. Link to comment Share on other sites More sharing options...
Management Matt Posted December 13, 2016 Management Share Posted December 13, 2016 Check out this comment here: Link to comment Share on other sites More sharing options...
GriefCode Posted December 13, 2016 Author Share Posted December 13, 2016 Just now, Matt said: Check out this comment here: I just did, thanks for the info. Please keep in mind that no only the Table-Helper for the database is connected, also IPS\DB:i()->select() has a parameter orderBy that is not functional, i just found that 3 days ago, but didn't looked deeper into it until yesterday. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.