Forum Discussion

engwei's avatar
engwei
Guide
2 years ago

Handling the upcoming LIMIT and OFFSET changes

You might be aware of the upcoming changes to LIMIT and OFFSET having a maximum value of 1000 in 23.12.

Obviously this is likely to cause a problem to any custom components that loop through data such as messages.

Cursor will work fine in some scenarios, but any kind of custom pagination will need UI/UX work to access records greater than 2000! 

Using cursor we'd only be able to move forward and not skip to page 1000 for example.

Any other thoughts on how this could be achieved with CURSOR?!

API
Community API
REST API v2 & LiQL
  • luk's avatar
    luk
    Boss
    2 years ago

    Thanks for the update, I hope they really think twice about that, LIMIT cap is fine, but not the OFFSET cap.

  • Akenefick's avatar
    Akenefick
    Genius
    2 years ago

    engwei luk 

    I heard back from support. It sounds like they are putting this change on hold while they consider feedback. They said:

     

    "After receiving feedback from the first select customers contacted the decision was made to pause and review if certain suggestions will be possible. If/when we decide to move forward, we'll inform the entire customer base."

    So, hopefully they will decide not to move forward with this.

  • luk's avatar
    luk
    Boss
    2 years ago

    Why capping the OFFSET? Doesn' t make any sense to me, ok LIMIT cap at 1000 is no big deal, it was always documented like that (was already enforced in API v1) and thus anything developed with something going beyond 1000 is aking to fail at some point, but the OFFSET is exactly there to paginate through stuff?

    This is a breaking change that will incur cost for customers everywhere or break a lot of communities, paging with the CURSOR is not that easy from the frontend, should we pass the CURSOR via GET param?

    Enforcing 1000 LIMIT is one thing, capping OFFSET to 1000 is nonsense and a no-go!

    • Akenefick's avatar
      Akenefick
      Genius
      2 years ago

      I have a support case open asking for guidance on this. I let them know this will break the pagination on our forum lists and on other customizations. So far, their only response was "use cursor", but I don't think that's possible. I will update here if I get any better response.

      I also wonder how many customers are using offset and don't even know it.

      • luk's avatar
        luk
        Boss
        2 years ago

        Regarding "I also wonder how many customers are using offset and don't even know it.":

        I think A LOT, I have worked on many communities where I have taken over a customized codebase, and I can count the occurrences where CURSOR was used to paginate something on one hand, most use LIMIT/OFFSET...

  • engwei's avatar
    engwei
    Guide
    2 years ago

    Yes that's my thought exactly - pagination like that will be impossible over a certain number (depending on page size)

    • engwei's avatar
      engwei
      Guide
      2 years ago

      It was emailed from Khoros Support notifications on Wednesday 9th August. Here's the content of the email:

      What’s changing?


      This update pertains to changes coming in release 23.12 that will affect how customers use the LIMIT and OFFSET values with API v2.


      1. LIMIT:
      Starting with Community release 23.12, we will enforce a maximum value of 1K to the LIMIT. The default will continue to be 25. Exceeding the maximum 1K LIMIT will result in an error.


      2. OFFSET: 


      Starting with Community release 23.12, we will enforce a maximum value of 1K to the OFFSET. All queries with OFFSET values greater than 1K will fail with an error that the specified OFFSET exceeds the maximum allowed OFFSET.


      About LIMIT, OFFSET, & CURSOR


      Community API v2 supports the OFFSET and CURSOR mechanisms to page through data in a result set from a LiQL query. The CURSOR method scales more efficiently and works best for large result sets. The OFFSET method is more straightforward and works well for small data sets and for testing LiQL queries. Queries to the messages, users, boards, and categories collections often return larger result sets, and the CURSOR method will be required to obtain deeper data sets starting in 23.12.


      LIMIT:  the number of records to include as you page through the result set.


      OFFSET: the number of records to skip before returning results.


      CURSOR: the number of records to skip before returning results, using a string-based mechanism.

      • Example: SELECT author.id FROM messages WHERE board.name = 'wireless' LIMIT 10 CURSOR ‘MjuM3wYjBB…’


      What's driving these changes? 


      In order to maintain the integrity and scalability of community resources, these new thresholds are being implemented to reinforce best practices for obtaining deeper result sets using the more efficient CURSOR method.


      What changes need to be made?


      If you use LIMIT or OFFSET with a value higher than 1,000, you will need to lower that value to meet the aforementioned thresholds.


      When are these changes being made?


      The changes are slated for release 23.12, which is tentatively scheduled to be made available in January 2024.


      You can find more details regarding LiQL pagination here. Should you have any questions, please do not hesitate to contact Khoros Support.Thank you for being a Khoros customer!

             -   The Khoros Team
       

       

      • Akenefick's avatar
        Akenefick
        Genius
        2 years ago

        Hmm that's troubling for us as well. I was under the impression that the LIMIT was already a maximum of 1000, so I've already been developing with that in mind.

        However, like you say, pagination becomes difficult with a cap on OFFSET. We have custom message lists on our forum page, and with some of our boards having 10s of thousands of posts this will break our pagination.

        I think the only way to do this with cursor is to limit it to next/prev. I don't see how something like the below is possible without larger offsets.