mysql - How do I order by an aggregate count in Rails when also joining to the same table? -

there scope this:

  scope :sorted_by_dongle_count,     > { includes(:dongles)           .order('count(dongles.id) asc')           .group('organisations.id')           .references(:dongles) } 

this works sqlite, fails mysql.

activerecord::statementinvalid: mysql2::error: expression #8 of select list not in group clause , contains nonaggregated column 'portal_development.dongles.id' not functionally dependent on columns in group clause; incompatible sql_mode=only_full_group_by: [query here]

looking @ query, easy see why:

select `organisations`.`id` t0_r0,        `organisations`.`name` t0_r1,        `dongles`.`id` t1_r0,           👈 should aggregate!        `dongles`.`name` t1_r1,        `dongles`.`organisation_id` t1_r4 `organisations`   left outer join `dongles`     on `dongles`.`organisation_id` = `organisations`.`id` group organisations.id order count(dongles.id) desc 

is there proper way without resorting select() , hard-coding entire list of columns?

(one other workaround know count caching. considering already, having count readily available speed sorting.)

i found if change to:

  scope :sorted_by_dongle_count,    -> { joins(:dongles)       👈           .order('count(dongles.id) asc')           .group('organisations.id')           .references(:dongles) } 

i stop getting error , results same. guess when using counts or other aggregates, avoid using includes? other queries, use includes...