# Order and paginate by children count **URL:** \ **Category:** Elasticsearch\ **Created:** [April 19, 2013, 11:38am UTC](https://discuss.elastic.co/t/order-and-paginate-by-children-count/11642 "2013-04-19T11:38:11Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Stalinko](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stalinko/32/989_2.png) [@Stalinko](https://discuss.elastic.co/u/Stalinko)\ **Post date:** [April 19, 2013, 11:38am UTC](https://discuss.elastic.co/t/order-and-paginate-by-children-count/11642/1 "2013-04-19T11:38:11Z")
I have been breaking tables with my head for 3 days already. I have that **task:** "authors" have "books". I need to search authors who has books satisfying some criteria. The problem is that I need to sort authors by "how many matching books he has". Exact sorting formula is "number\_matching\_books^2 / total\_books\_author\_has". And also I need to paginate because there can be hundreds thousands of resulting authors. _I was solving this task in that way:_ type "book" with "author\_id" field and facet on "author\_id" with "facet\_filter" on book criteria. In facet I have written quite hard script in "value\_script" which counted "total" value in a very tricky way that sorting by total gave exactly that results what I need. All was good until I faced with pagination. I tried to do pagination in the same tricky way: in script I was counting "total" field in a way that "offset'ed" results had been pushed into the end...... but all this is so tricky and too hard to implement in production. **Maybe there are another ways to solve the task?** Or I should switch off from ElasticSearch... ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [April 22, 2013, 12:11pm UTC](https://discuss.elastic.co/t/order-and-paginate-by-children-count/11642/2 "2013-04-22T12:11:20Z")
Just wondering why you don't index author field in books? I mean that an author won't change once the book is written. So probably, you can index book with a field author which is an object with fields, name and whatever property you want. Does it help? Perhaps, your use case is not really about books… 😉 -- David Pilato | Technical Advocate | [Elasticsearch.com](http://Elasticsearch.com) @dadoonet | @elasticsearchfr | @scrutmydocs Le 19 avr. 2013 à 13:38, Stalinko [staliniv@gmail.com](mailto:staliniv@gmail.com) a écrit : > I have been breaking tables with my head for 3 days already. I have that > _task:_ > > "authors" have "books". I need to search authors who has books satisfying > some criteria. > The problem is that I need to sort authors by "how many matching books he > has". Exact sorting formula is "number\_matching\_books^2 / > total\_books\_author\_has". > And also I need to paginate because there can be hundreds thousands of > resulting authors. > > /I was solving this task in that way:/ > type "book" with "author\_id" field and facet on "author\_id" with > "facet\_filter" on book criteria. > In facet I have written quite hard script in "value\_script" which counted > "total" value in a very tricky way that sorting by total gave exactly that > results what I need. All was good until I faced with pagination. I tried to > do pagination in the same tricky way: in script I was counting "total" field > in a way that "offset'ed" results had been pushed into the end...... but all > this is so tricky and too hard to implement in production. > > _Maybe there are another ways to solve the task?_ Or I should switch off > from Elasticsearch... > > -- > View this message in context: [http://elasticsearch-users.115913.n3.nabble.com/Order-and-paginate-by-children-count-tp4033646.html](http://elasticsearch-users.115913.n3.nabble.com/Order-and-paginate-by-children-count-tp4033646.html) > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). > > -- > You received this message because you are subscribed to the Google Groups "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [April 22, 2013, 12:20pm UTC](https://discuss.elastic.co/t/order-and-paginate-by-children-count/11642/3 "2013-04-22T12:20:23Z")
It sounds like what you need is to create a parent/child relationship between your authors and books, then use a has\_child query to search for matching books, and count each matched book as a score of 1. You'd need to store the total\_books by an author in the author doc itself, in order to implement your algorithm: curl -XGET '[http://127.0.0.1:9200/my\_index/author/\_search?pretty=1](http://127.0.0.1:9200/my_index/author/_search?pretty=1)' -d ' { "query" : { "has\_child" : { "script" : { "script" : "\_score \* \_score / doc[\u0027total\_books\u0027]" }, "query" : { "custom\_score\_query" : { "query" : { "constant\_score" : { "query" : { "match" : { "book\_title" : "the wind in the willows" } } } } } }, "score\_mode" : "sum", "type" : "book" } } } ' clint On Fri, Apr 19, 2013 at 1:38 PM, Stalinko [staliniv@gmail.com](mailto:staliniv@gmail.com) wrote: > I have been breaking tables with my head for 3 days already. I have that > _task:_ > > "authors" have "books". I need to search authors who has books satisfying > some criteria. > The problem is that I need to sort authors by "how many matching books he > has". Exact sorting formula is "number\_matching\_books^2 / > total\_books\_author\_has". > And also I need to paginate because there can be hundreds thousands of > resulting authors. > > /I was solving this task in that way:/ > type "book" with "author\_id" field and facet on "author\_id" with > "facet\_filter" on book criteria. > In facet I have written quite hard script in "value\_script" which counted > "total" value in a very tricky way that sorting by total gave exactly that > results what I need. All was good until I faced with pagination. I tried to > do pagination in the same tricky way: in script I was counting "total" > field > in a way that "offset'ed" results had been pushed into the end...... but > all > this is so tricky and too hard to implement in production. > > _Maybe there are another ways to solve the task?_ Or I should switch off > from Elasticsearch... > > -- > View this message in context: > [http://elasticsearch-users.115913.n3.nabble.com/Order-and-paginate-by-children-count-tp4033646.html](http://elasticsearch-users.115913.n3.nabble.com/Order-and-paginate-by-children-count-tp4033646.html) > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [April 22, 2013, 12:22pm UTC](https://discuss.elastic.co/t/order-and-paginate-by-children-count/11642/4 "2013-04-22T12:22:45Z")
Sorry, that was incorrectly nested: curl -XGET '[http://127.0.0.1:9200/my\_index/author/\_search?pretty=1](http://127.0.0.1:9200/my_index/author/_search?pretty=1)' -d ' { "query" : { "custom\_score\_query" : { "script" : { "script" : "\_score \* \_score / doc[\u0027total\_books\u0027]" }, "query" : { "has\_child" : { "query" : { "constant\_score" : { "query" : { "match" : { "book\_title" : "the wind in the willows" } } } }, "score\_mode" : "sum", "type" : "book" } } } } } ' On Mon, Apr 22, 2013 at 2:20 PM, Clinton Gormley [clint@traveljury.com](mailto:clint@traveljury.com)wrote: > It sounds like what you need is to create a parent/child relationship > between your authors and books, then use a has\_child query to search for > matching books, and count each matched book as a score of 1. You'd need to > store the total\_books by an author in the author doc itself, in order to > implement your algorithm: > > curl -XGET '[http://127.0.0.1:9200/my\_index/author/\_search?pretty=1](http://127.0.0.1:9200/my_index/author/_search?pretty=1)' -d ' > { > "query" : { > "has\_child" : { > "script" : { > "script" : "\_score \* \_score / doc[\u0027total\_books\u0027]" > }, > "query" : { > "custom\_score\_query" : { > "query" : { > "constant\_score" : { > "query" : { > "match" : { > "book\_title" : "the wind in the willows" > } > } > } > } > } > }, > "score\_mode" : "sum", > "type" : "book" > } > } > } > ' > > clint > > On Fri, Apr 19, 2013 at 1:38 PM, Stalinko [staliniv@gmail.com](mailto:staliniv@gmail.com) wrote: > > > I have been breaking tables with my head for 3 days already. I have that > > _task:_ > > > > "authors" have "books". I need to search authors who has books satisfying > > some criteria. > > The problem is that I need to sort authors by "how many matching books he > > has". Exact sorting formula is "number\_matching\_books^2 / > > total\_books\_author\_has". > > And also I need to paginate because there can be hundreds thousands of > > resulting authors. > > > > /I was solving this task in that way:/ > > type "book" with "author\_id" field and facet on "author\_id" with > > "facet\_filter" on book criteria. > > In facet I have written quite hard script in "value\_script" which counted > > "total" value in a very tricky way that sorting by total gave exactly that > > results what I need. All was good until I faced with pagination. I tried > > to > > do pagination in the same tricky way: in script I was counting "total" > > field > > in a way that "offset'ed" results had been pushed into the end...... but > > all > > this is so tricky and too hard to implement in production. > > > > _Maybe there are another ways to solve the task?_ Or I should switch off > > from Elasticsearch... > > > > -- > > View this message in context: > > [http://elasticsearch-users.115913.n3.nabble.com/Order-and-paginate-by-children-count-tp4033646.html](http://elasticsearch-users.115913.n3.nabble.com/Order-and-paginate-by-children-count-tp4033646.html) > > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Stalinko](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stalinko/32/989_2.png) [@Stalinko](https://discuss.elastic.co/u/Stalinko)\ **Post date:** [April 23, 2013, 8:13am UTC](https://discuss.elastic.co/t/order-and-paginate-by-children-count/11642/5 "2013-04-23T08:13:05Z")
Thank you Clint! Your solution looks really pretty. I have solved the task already with "top\_children" query and a very monstrous script counting scores. Think I will rewrite it in your way. Just for lulz, my solution: [http://127.0.0.1:9200/my\_index/author/\_search](http://127.0.0.1:9200/my_index/author/_search) { "query": { "top\_children": { "type": "book", "factor": 10000, "score": "max", "query":{ "custom\_filters\_score": { "query" : {"match\_all" : {}}, "filters" : [{ "filter" : {"term": {"book\_title": "Hello world"}}, "script": " aid = doc['authorId'].value; cnt = doc['booksCount'].value; if(found[aid] == null) found.put(aid, 0); found[aid] = found[aid] + 1; found[aid]\*found[aid] / cnt " }], "params":{"found": {}, "aid": 0, "cnt" : 0} } } } } } In my script "found" is a map "aid =\> count of matching books", "aid" and "cnt" are just variables for better code. With each matching record I increment according "found"s element and return new recalculated score. In each "book" record I store also "authorId" and "booksCount" the author has. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 2:39am UTC](https://discuss.elastic.co/t/order-and-paginate-by-children-count/11642/6 "2017-07-06T02:39:59Z")