# A request for help with how to solve my seemingly tricky problem in ElasticSearch! **URL:** \ **Category:** Elasticsearch\ **Created:** [October 14, 2012, 2:09pm UTC](https://discuss.elastic.co/t/a-request-for-help-with-how-to-solve-my-seemingly-tricky-problem-in-elasticsearch/9353 "2012-10-14T14:09:18Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![James\_2](https://avatars.discourse-cdn.com/v4/letter/j/ebca7d/32.png) [@James\_2](https://discuss.elastic.co/u/James_2)\ **Post date:** [October 14, 2012, 2:09pm UTC](https://discuss.elastic.co/t/a-request-for-help-with-how-to-solve-my-seemingly-tricky-problem-in-elasticsearch/9353/1 "2012-10-14T14:09:18Z")
Hi all! I'm trying to solve a problem of searching across 2 'entities' and sorting and paging the results. I have been unable to come up with a solution so far, so thought I would ask here, and would greatly appreciate anyone's help! I have considered storing these entities in various ways; as separate documents (using a parent/child relationship), as a combined document and as nested documents. In each case I hit problems! The complexity is around how we want to group, sort, page and count the results. I have tried to outline the problem below. Please let me know what you think! Cheers, James. curl -XPUT localhost:9200/jobs/company/1 -d'{ "name": "Company A", "location": "America" }' curl -XPUT localhost:9200/jobs/company/2 -d'{ "name": "Company B", "location": "Britain" }' curl -XPUT localhost:9200/jobs/employee/1 -d'{ "name": "Alice", "job\_title": "Astronaut" }' curl -XPUT localhost:9200/jobs/employee/2 -d'{ "name": "Bob", "job\_title": "Builder" }' ==== Alice works for Company A _and_ Company B Bob works for Company B The relationship between Employee and Company is many-to-many. ==== 1. Example search: Employees that are Astronauts that work for Companies in America or Britain Desired result count: 1 (Only 1 Alice, despite the fact she works for 2 Companies) Desired display: | Company A | | --- | | Alice | | Company B | | --- | | Alice | So the result count is the count of unique Employees, and each page should display a fixed number of Employees, but the results are grouped by Company and primarily sorted by properties of Company (name, location etc.) 1. Example search: All | Company A | | --- | | Alice | | Company B | | --- | | Alice | | Bob | -- ---
**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:** [October 14, 2012, 5:07pm UTC](https://discuss.elastic.co/t/a-request-for-help-with-how-to-solve-my-seemingly-tricky-problem-in-elasticsearch/9353/2 "2012-10-14T17:07:47Z")
Hi James, Here is what I think about your use case. Seems that you are thinking relationnal in a NoSQL world. Your point of interest seems to be Employees. So let's index employees. curl -XPUT localhost:9200/jobs/employee/1 -d'{ "name":"Alice", "job\_title":"Astronaut", "companies":[ { "name":"Company A", "location":"America" }, { "name":"Company B", "location":"Britain" } ] }' curl -XPUT localhost:9200/jobs/employee/2 -d'{ "name":"Bob", "job\_title":"Builder", "companies":[ { "name":"Company A", "location":"America" } ] }' So now, you can find all employees in America that are Astronaut with a boolQuery on job\_title and companies.location. Does it make sense for your use case? HTH David. Le 14 octobre 2012 à 16:09, James [james.p523@yahoo.com](mailto:james.p523@yahoo.com) a écrit : > Hi all! > > I'm trying to solve a problem of searching across 2 'entities' and sorting > and paging the results. I have been unable to come up with a solution so far, > so thought I would ask here, and would greatly appreciate anyone's help! > > I have considered storing these entities in various ways; as separate > documents (using a parent/child relationship), as a combined document and as > nested documents. In each case I hit problems! > > The complexity is around how we want to group, sort, page and count the > results. > > I have tried to outline the problem below. > > Please let me know what you think! > > Cheers, James. > > curl -XPUT localhost:9200/jobs/company/1 -d'{ > "name": "Company A", > "location": "America" > }' > > curl -XPUT localhost:9200/jobs/company/2 -d'{ > "name": "Company B", > "location": "Britain" > }' > > curl -XPUT localhost:9200/jobs/employee/1 -d'{ > "name": "Alice", > "job\_title": "Astronaut" > }' > > curl -XPUT localhost:9200/jobs/employee/2 -d'{ > "name": "Bob", > "job\_title": "Builder" > }' > > ==== > > Alice works for Company A _and_ Company B > > Bob works for Company B > > The relationship between Employee and Company is many-to-many. > > ==== > > 1. > > Example search: Employees that are Astronauts that work for Companies in > America or Britain > > Desired result count: 1 (Only 1 Alice, despite the fact she works for 2 > Companies) > > Desired display: > > | Company A | > | --- | > | Alice | > > | Company B | > | --- | > | Alice | > > So the result count is the count of unique Employees, and each page should > display a fixed number of Employees, but the results are grouped by Company > and primarily sorted by properties of Company (name, location etc.) > > 1. > > Example search: All > > | Company A | > | --- | > | Alice | > > | Company B | > | --- | > | Alice | > | Bob | > > -- -- David Pilato [http://www.scrutmydocs.org/](http://www.scrutmydocs.org/) [http://dev.david.pilato.fr/](http://dev.david.pilato.fr/) Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs -- ---
**Author:** ![James\_2](https://avatars.discourse-cdn.com/v4/letter/j/ebca7d/32.png) [@James\_2](https://discuss.elastic.co/u/James_2)\ **Post date:** [October 15, 2012, 10:56am UTC](https://discuss.elastic.co/t/a-request-for-help-with-how-to-solve-my-seemingly-tricky-problem-in-elasticsearch/9353/3 "2012-10-15T10:56:02Z")
Hi David, Firstly, thanks very much for taking the time to reply; I really appreciate it! 🙂 If I do as you've suggested, then the result count is as desired, but I cannot see how to do pagination, as we want to group Employees by Company, sort those Companies by Company properties (e.g. name), and then display pages of results where each page has a fixed number of Employees on it. What would you suggest? Thanks, James. -- ---
**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:** [October 15, 2012, 11:20am UTC](https://discuss.elastic.co/t/a-request-for-help-with-how-to-solve-my-seemingly-tricky-problem-in-elasticsearch/9353/4 "2012-10-15T11:20:53Z")
Grouping is not yet available. See [https://github.com/elasticsearch/elasticsearch/pull/2326](https://github.com/elasticsearch/elasticsearch/pull/2326) So what I would probably do is to create one first query with a TermFacet on company to get all companies and then create a multi search with each company. See [http://www.elasticsearch.org/guide/reference/api/multi-search.html](http://www.elasticsearch.org/guide/reference/api/multi-search.html) It will cost you 2 queries but I think the number of companies will not grow so fast so you can cache it on client side? ## HTH David 😉 Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs Le 15 oct. 2012 à 12:56, James [james.p523@yahoo.com](mailto:james.p523@yahoo.com) a écrit : Hi David, Firstly, thanks very much for taking the time to reply; I really appreciate it! 🙂 If I do as you've suggested, then the result count is as desired, but I cannot see how to do pagination, as we want to group Employees by Company, sort those Companies by Company properties (e.g. name), and then display pages of results where each page has a fixed number of Employees on it. What would you suggest? Thanks, James. -- -- ---
**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, 3:08am UTC](https://discuss.elastic.co/t/a-request-for-help-with-how-to-solve-my-seemingly-tricky-problem-in-elasticsearch/9353/5 "2017-07-06T03:08:50Z")