# Is there any possible solution for group by, order by, and pagination?
**URL:** https://discuss.elastic.co/t/is-there-any-possible-solution-for-group-by-order-by-and-pagination/269407
**Category:** Elasticsearch
**Created:** [April 7, 2021, 6:38am UTC](https://discuss.elastic.co/t/is-there-any-possible-solution-for-group-by-order-by-and-pagination/269407 "2021-04-07T06:38:26Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@Jenny1](https://discuss.elastic.co/u/Jenny1)
#### Post date: [April 7, 2021, 6:38am UTC](https://discuss.elastic.co/t/is-there-any-possible-solution-for-group-by-order-by-and-pagination/269407/1 "2021-04-07T06:38:26Z")
Hi, I got stuck on the problem with elasticsearch, and appreciated it if someone could help me.
I have 2 indices, comment\_index, and like\_index.
- **comment\_index** : user comment data
- **like\_index** : keeps track of which user likes which comment
{'user\_id': '1', 'commend\_id': 1},{'user\_id': '1', 'commend\_id': 2},{'user\_id': '2', 'commend\_id': 1}, ...
**mappings (like\_index)**
```
"mappings": {
"properties": {
"comment_id": {
"type": "keyword"
},
"freq_num": {
"type": "integer" --> copied from comment_index, since the result needs to be ordered by this field.
},
"user_id": {
"type": "keyword"
}
....
}
```
}
And below is what I need to do.
query like\_index, and the result needs to be
1. group by comment\_id (to get how many likes each comment has)
2. order by comment\_id count (count of each comment\_id, that is, the number of like counts of each comments) and order by an additional field(freq\_num)
if it cannot be possible, then order only with 'doc\_count' is also fine.
3. scroll or pagination (size=10)
I've tried several ideas but couldn't find the right way to solve this problem.
- The actual scripts I've made are more complicated, but for those who searching for similar issues, I simplified the scripts.
Anyways, I've tried 3 ways to solve the problem as below.
**#1. aggregation composite**
It's possible group by and pagination(after key) but can't order by doc\_count.
```
{
"query": { "match_all": {} },
"aggs": {
"like_counts": {
"composite": {
"sources": [
{"freq_num": {"terms": {"field": "freq_num", "order": "desc"}}},
{"comment_id": {"terms": {"field": "comment_id"}}}
],
"size": 10
}
}
}
```
**what I expected:**
```
"buckets": [
{
"key": {
"freq_num": 136,
"comment_id": "JcAYqngBhI5vW3tODWnX"
},
"doc_count": 2
},
{
"key": {
"freq_num": 446,
"comment_id": "IsAYqngBhI5vW3tODWm7"
},
"doc_count": 1
},
{
"key": {
"freq_num": 136,
"comment_id": "I8AYqngBhI5vW3tODWnE"
},
"doc_count": 1
},
....
```
**what I've got:**
```
"buckets": [
{
"key": {
"freq_num": 446,
"comment_id": "IsAYqngBhI5vW3tODWm7"
},
"doc_count": 1
},
{
"key": {
"freq_num": 136,
"comment_id": "I8AYqngBhI5vW3tODWnE"
},
"doc_count": 1
},
{
"key": {
"freq_num": 136,
"comment_id": "JcAYqngBhI5vW3tODWnX"
},
"doc_count": 2
},
....
```
# **2. aggregation terms**
At first, this gives the result in the sort order of the number of docs as I expected.
But If I use the partition option here, this doesn't provide the right result.
```
{
"query": { "match_all": {}
},
"size": 0,
"aggs": {
"like_counts ": {
"terms": {
"field": "comment_id",
"include": {
"partition": 0,
"num_partitions": 10
},
"size": 3,
"order": {"freq_num": "desc"}
},
"aggs": {
"freq_num": { "max": { "field": "freq_num" }}
}
}
}
}
```
**what I expected:**
- partition: 1
- partition: 2
**what I've got:**
- partition: 1
- partition: 2
**#3. collapse**
I've just noticed that I also have the 'collapse' option, but I don't know how to use it here.
As I run the script below, I get a similar result but didn't get the count of each group. (collapse by comment\_id)
```
{
"query": { "match_all": {} },
"size": 10,
"collapse": {
"field": "comment_id"
}
}
```
I'm considering rather choose #2 and fetch all data without using the partition option and split the data which might be a really bad idea.
Could anyone give me the proper way to achieve this?
Thank you.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [May 5, 2021, 6:39am UTC](https://discuss.elastic.co/t/is-there-any-possible-solution-for-group-by-order-by-and-pagination/269407/2 "2021-05-05T06:39:05Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.