2

I am using Model.group(:category) in order to get unique records based on category field in Rails 5 application. Table data:

id catgeory description
1    abc      test
2    abc      test1
3    abc      test2
4    xyz      test
5    xyz       testabc

I want records (1,4) as a result. Therefore I am using Model.group(:category) which works fine for MYSQL whose sql_mode is " " . Unforunately its throwing an error "SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" whose sql_mode is "only_full_group_by". whats the best way to change the query to match the mode?

Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
Veda
  • 577
  • 1
  • 6
  • 22

1 Answers1

0

Perhaps try specifying which id you want? You could use MIN(id), MAX(id) etc.

MySQL supports a non-standard extension to SQL described here. To continue using that behavior, you could change the sql_mode to TRADITIONAL in config/database.yml.

jspcal
  • 50,847
  • 7
  • 72
  • 76
  • 1
    How to use min/ max along with group ? I tried Model.select('MAX(id)').group(:category) which is not working. – Veda May 09 '19 at 00:19
  • `Model.maximum("id")` – jspcal May 09 '19 at 00:28
  • I want to dsplay complete record based on unique category For eg: 1 abc test 4 xyz test – Veda May 09 '19 at 00:32
  • Yes you can either aggregate all the selected columns, use traditional sql mode. or a query like this https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group – jspcal May 09 '19 at 00:33
  • Which one is a better option a) changing the sql_mode b) changing the query? – Veda May 09 '19 at 00:53
  • @jspcal I'm also interested on which should be the best way to go? I upgraded from Rais 4.2 to Rails 5.0.1 – alexventuraio Jul 31 '21 at 01:07