Support limit and offset in our CA queries

freddyDOTCMS commented 3 weeks ago

Parent Issue

No response

Problem Statement

Cube.js already support limit and offset but we need to support them in our syntax, the way as it works for Cube.js is:


{
  "measures": ["Orders.count"],
  "dimensions": ["Orders.status"],
  "limit": 10,   // Limit the results to 10 rows
  "offset": 20   // Skip the first 20 rows
}

Steps to Reproduce

Start dotCMS and Analytics
Config the Analytics App
Make the follow request

URL:  '/api/v1/analytics/content/_query' 
Method: POST
body: {
    "query": {
        "dimensions":["request.url"],
        "order": "request.url desc"
    }
}

Now run this one

URL:  '/api/v1/analytics/content/_query' 
Method: POST
body: {
"query": {
    "dimensions":["request.url"],
    "order": "request.url desc",
    "limit": 1,
    "offset": 1 
}
}

Must response just with the second register returned in the first query

NOTE TO QA:

You can also refer to my comment showcasing how the limit and offset parameters work and can be added to the queries.

Acceptance Criteria

Limit and offset must works as expected

dotCMS Version

latest in master

Proposed Objective

Core Features

Proposed Priority

Priority 2 - Important

External Links... Slack Conversations, Support Tickets, Figma Designs, etc.

No response

Assumptions & Initiation Needs

No response

Quality Assurance Notes & Workarounds

No response

Sub-Tasks & Estimates

No response

jcastro-dotcms commented 6 days ago

The limit and offset parameters in both the dotCMS query and the official CubeJS query work as expected. Here's how you can test them:

Generate as much Content Analytics data as possible; i.e., go to the Demo Site and click around, going into as many pages as possible.
For the CubeJS Query REST Endpoint:
- Run the following query:
```
{
"filters": [
{
"member": "request.whatAmI",
"operator": "equals",
"values": [
"PAGE"
]
}
],
"measures": [
"request.count"
],
"dimensions": [
"request.url",
"request.pageId",
"request.pageTitle"
],
"order": {
"request.count": "desc"
}
}
```
  And keep track of the top 4 records.
- Now, add the limit and offset parameters to the query. For instance:
```
...
...
"order": {
"request.count": "desc"
},
"limit": 2,
"offset": 2
```
- This will return the third and fourth records, as the offset is an exclusive value. This means that results will be returned as of the third record.

For the dotCMS Query REST Endpoint:

This is the version of the query -- notice how it differs from the CubeJS query:

{
"query": {
"measures": [
    "request.count"
],
"order": "request.count DESC",
"dimensions": [
    "request.url",
    "request.pageId",
    "request.pageTitle"
],
"filters": "request.whatAmI = ['PAGE']",
}
}

Adding the same limit and offset values as in the CubeJS query will return the exact same results: The third and fourth records, which is expected.

josemejias11 commented 19 hours ago

Approved: Tested on master_b295876_SNAPSHOT, Docker, macOS 14.5, FF v126.0.1

dotCMS / core