How to groupBy on table?

[GriefCode]

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

[bfarber]

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.

[Matt]

Check out this comment here: 

 

[GriefCode]

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.