How to correctly use group by in adonisjs


#1

Hi, everyone
I need help how to correctly select db using group by
I always fail using group by
Here is some response I got

ER_WRONG_FIELD_WITH_GROUP:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘bapake_adonis_dev.derajats.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Here is my query

let derajats = await Derajat
  .query()
  .whereIn('himpunan_id', himpunan_ids)
  .where('nilai', '>', 0)
  .groupBy('kost_id')
  .fetch()

And here is my table without group by

id kost_id himpunan_id nilai created_at updated_at
2 2 2 0.71 2018-08-06 01:07:22 2018-08-06 01:07:22
29 3 2 0.63 2018-08-06 01:07:23 2018-08-06 01:07:23
110 9 2 0.71 2018-08-06 01:07:27 2018-08-06 01:07:27
8 2 8 0.60 2018-08-06 01:07:22 2018-08-06 01:07:22
35 3 8 0.50 2018-08-06 01:07:24 2018-08-06 01:07:24
89 8 8 1.00 2018-08-06 01:07:26 2018-08-06 01:07:26
116 9 8 0.50 2018-08-06 01:07:28 2018-08-06 01:07:28
143 10 8 0.50 2018-08-06 01:07:29 2018-08-06 01:07:29
170 11 8 0.60 2018-08-06 01:07:30 2018-08-06 01:07:30


Sorry if some words are unreadable :smiley:
Thanks


#2

I got it

let kost_ids = await Derajat
  .query()
  .select('kost_id')
  .whereIn('himpunan_id', himpunan_ids)
  .where('nilai', '>', 0)
  .groupBy('kost_id')
  .pluck('kost_id')