1

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

zalis
  • 1,191
  • 1
  • 11
  • 20

2 Answers2

1

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.

Jan
  • 4,369
  • 14
  • 25
  • thanks for your answer, i fully agree that JCR has got these limitations when it comes to my requirements. but i am using Magnolia which is built on that, so i am kind of forced to use it ... i would much rather prefer hibernate which will give me the flexibility – zalis Dec 08 '15 at 03:12
  • You can still use Magnolia to access data stored elsewhere. It's not limited to JCR storage. I believe in your other post, someone already pointed you towards guide on how to create no-jcr app and how to access content outside of JCR from within Magnolia. – Jan Dec 11 '15 at 10:42
0

Unfortunately, there is no aggregation functions in JCR SQL|XPath|JCR-SQL2/AQM.

The only thing I can suggest you, is to:

  1. either iterate over these nodes and do all calculation in your code,
  2. either, if these nodes are spreaded over the repository, query them by node type or restaurnat_name and, again, do all calculations in your code.
Oleksandr Tarasenko
  • 1,454
  • 15
  • 21