I am trying to write a query in JCR_SQL2 with no luck. I have a table like below :
|property |example value|
+---------------+-------------+
|user_name |John Smith |
|restaurnat_name|McDonalds |
|action |like |
|food |big mac |
the query would be something like this : "get most liked food for user, for restaurant"
and my response would be something like below:
|food |User Count|
+-------+----------+
|big mac| 20 users |
|nuggets| 19 users |
Which is ordered by user count as in table above
Nonetheless, there is one more caveat, i need to enforce pagination to that i can return like 10 results and be able to jump to next page where i would get my other 10, making sure the sort is not impacted.
Any help will be greatly appreciated
I don't think JCR is right tool for this kind of queries. It's hierarchical storage with some extra functions on top, but not general purpose database and for sure not db optimised for data mining queries like the one you describe above. You should use something else for this.