Assume that below is my Datastore content at Google Cloud Platform:
class ItemRecord {
@Id
private
Long id;
@Index
private String item;
@Index
private String user;
@Index
private int minValue;
@Index
private int maxValue;
}
I want to get minumum of minValue and maximum of maxValue values for item groups:
item1 minumum_minValue= 1, maximum_maxValue= 13
item2 minumum_minValue= 3, maximum_maxValue= 10
Note: 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 minValue and maximum of maxValue values.
I was looking for something like below sql:
SELECT item, MAX(maxValue)
FROM [ItemRecord]
Group by item
and
SELECT item, MIN(minValue)
FROM [ItemRecord]
Group by item
But Datastore does not support "group by" operation and I couldn't find anything similar for it.
So, how I solved the issue?
1) Get Distinct item names:
private static List<String> getDistinctItemNameList() {
Query query = ofy().load().type(ItemRecord.class)
.project("item").distinct(true);
List<ItemRecord> resultList = query.list();
if(resultList != null && !resultList.isEmpty()) {
List<String> itemNameList = new ArrayList<>(resultList.size());
for (ItemRecord itemRecord : resultList) {
itemNameList.add(itemRecord.getItem());
}
return itemNameList;
}
return null;
}
2) For each itemName (which is retrieved above) query datastore for its minumum minValue and maximum maxValue:
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;
}
Cost: (a query to retrieve distinct item names) + (distinct item count * query for minumum minValue) + (distinct item count * query for maximum maxValue)
But this solution is very annoying with its many read operations. Do you have any suggestion, better solution?
