sqlite group by equivalent

[hamidmalek]

Hello there,
I'm new to Objectbox and just switched from old fashioned SQLite. I tried to convert my old queries and rewrite them in Objectbox queries, but there is query that I don't know how to convert.

SELECT FOOD_ID , COUNT(FOOD_ID) AS cnt FROM TABLE_FOOD_LOG GROUP BY FOOD_ID HAVING cnt > 2 ORDER BY cnt DESC LIMIT 20

To clarify, there are Food and FoodLog tables, user adds logs from the Food objects as she/he consumes them, and FoodLog object has a reference in the Food objects, So I want to extract the most used foods by user.

[greenrobot-team]

This is not directly possible in ObjectBox. However, you can try to use property queries or maybe a query filter. In general, have a look at the Queries docs. -ut

[hamidmalek]

Can any expert tell me the best approach to implement this code?

[navidei]

Counting objects with specific properties (like this query of @hamidmalek with additional group by command) is a useful feature which would be appreciated if it's implemented in this great library. We have decided to switch from sqlite to this ORM in our team and lack of this feature made us to do so in java.

[greenrobot]

@hamidmalek You have to do two steps. First get all food_id values using a property query. Then do the grouping in Java.

We're open for API suggestions how to support this.

[greenrobot-team]

Closing this issue due to inactivity. 💤 Feel free to re-open with more details or submit a new issue.