title: "SQL Group By" Alternative for Datastore of Google Cloud Platform
body: <p>Assume that below is my Datastore content at Google Cloud Platform:</p>
  
  <p><a href="https://i.stack.imgur.com/dSfPT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dSfPT.png" alt="enter image description here"></a></p>
  
  <pre><code>class ItemRecord {
      @Id
      private
      Long id;
  
      @Index
      private String item;
      @Index
      private String user;
      @Index
      private int minValue;
      @Index
      private int maxValue;
  }
  </code></pre>
  
  <p>I want to get minumum of <code>minValue</code> and maximum of <code>maxValue</code> values for <code>item</code> groups:</p>
  
  <pre><code>item1 minumum_minValue= 1, maximum_maxValue= 13
  item2 minumum_minValue= 3, maximum_maxValue= 10
  </code></pre>
  
  <p><strong><em>Note:</em></strong> The minValue and maxValue properties are updatable (entities are user given values and user can update those any time) so please consider update, delete operations if you are going to suggest using a separate entity to store minumum of <code>minValue</code> and maximum of <code>maxValue</code> values.</p>
  
  <p>I was looking for something like below sql:</p>
  
  <pre><code>SELECT item, MAX(maxValue)
  FROM [ItemRecord]
  Group by item
  </code></pre>
  
  <p>and</p>
  
  <pre><code>SELECT item, MIN(minValue)
  FROM [ItemRecord]
  Group by item
  </code></pre>
  
  <p>But Datastore does not support "group by" operation and I couldn't find anything similar for it. </p>
  
  <p>So, how I solved the issue?</p>
  
  <p><strong>1)</strong> Get Distinct item names:</p>
  
  <pre><code>private static List&lt;String&gt; getDistinctItemNameList() {
      Query query = ofy().load().type(ItemRecord.class)
              .project("item").distinct(true);
      List&lt;ItemRecord&gt; resultList = query.list();
  
      if(resultList != null &amp;&amp; !resultList.isEmpty()) {
          List&lt;String&gt; itemNameList = new ArrayList&lt;&gt;(resultList.size());
          for (ItemRecord itemRecord : resultList) {
              itemNameList.add(itemRecord.getItem());
          }
          return itemNameList;
      }
      return null;
  }
  </code></pre>
  
  <p><strong>2)</strong> For each itemName (which is retrieved above) query datastore for its minumum minValue and maximum maxValue:</p>
  
  <pre><code>private ItemRecord getMinumumMinValue(String itemName) {
      ItemRecord record = ofy()
              .load()
              .type(ItemRecord.class)
              .filter("item", itemName)
              .order("minValue")
              .first().now();
  
      return record;
  }
  
  private ItemRecord getMaximumMaxValue(String itemName) {
      ItemRecord record = ofy()
              .load()
              .type(ItemRecord.class)
              .filter("item", itemName)
              .order("-maxValue")
              .first().now();
  
      return record;
  }
  </code></pre>
  
  <p><strong>Cost:</strong> (a query to retrieve distinct item names) + (distinct item count * query for minumum minValue) + (distinct item count * query for maximum maxValue)</p>
  
  <p>But this solution is very annoying with its many read operations. Do you have any suggestion, better solution? </p>