Announcement Announcement Module
Collapse
No announcement yet.
Page not being populated correctly Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Page not being populated correctly

    Not sure if it is me?

    I have this repository method

    @Query("start user=node({0}) " +
    "match (user)-[:FRIEND]-(friends) " +
    "return friends " +
    "order by friends.lastName asc")
    public Page<User> findFriends(User user, Pageable page);

    If I pass a Pageable requesting page=0 size=5 and I know totally there are 9 friends, I would expect the Page that is returned to have

    page.getNumber();
    returns 0 correctly.

    page.getSize();
    returns 5 correctly.

    page.getTotalElements();
    should return 8, but returns 5

    page.isLastPage();
    should return false, but returns true.

    page.isFirstPage();
    returns true correctly

    page.getTotalPages();
    should return 2, but returns 1.

    page.hasNextPage();
    should return true, returns false.

    page.hasPreviousPage();
    correctly returns false.

    Why is Paging not working as expected.

    I just wrote a test to test it out and calling that repo method with

    Page<User> pageOfUsers = userRepository.findFriends(gUser, new PageRequest(0,5));
    assertTrue("Should have a next page", pageOfUsers.hasNextPage());

    fails the first assert. hasNextPage returned false.

    When I change the request to be new PageRequest(0,10) I get all 8 friends returned, so there is definitely 8 friends, and paging wasn't working.

    Thanks

    Mark

  • #2
    OK, I think 0 based versus 1 based in the code base is the issue.

    I just did a PageRequest(1,5) and it now states that there are 2 pages and this is page 1 of 2 but only has the last 3 friends in the content.

    I'll look at Github code to check to see if I can see the issue and correct in my fork.

    Thanks

    Mark

    Comment


    • #3
      Well, I think I see the problem. But not sure how to correct it. Unless Neo4J has a simple way to return not only the resultset when you do skip and limit, but also the total of results without skip and limit.

      Should it do a count query first to get the total number, then the real query?

      In GraphRepositoryQuery under the createPage method last two lines are

      final int currentTotal = pageable.getOffset() + pageable.getPageSize();
      return new PageImpl(resultList, pageable, currentTotal);

      So the Page that we get back after running a paged query is only the total up to the page number we are currently at, So that is why the second query I ran getting page 1 shows page 1 of 2. And why the first query getting page 0 has the total being the page size and not total of nodes that would have been returned without paging. Which then leads us developers having to run two queries to determine if there is a next page, etc.

      Thanks

      Mark

      Comment


      • #4
        It is not just you. The problem with paging is that it is not so easy to determine the count without fetching the whole dataset.

        Of course you can do count queries, but then (especially for hand-written queries) you have to analyse the string query and replace the return data with count and remove sort and limit.

        Even worse if there were aggregates used in the query it might be impossible to determine the count without fetching the data. And you probably don't want to fetch all data if you are paging in the first place.

        The current approach is just to say, is there more data than just the current page, then there is a next page (and count should be pagesize * page +1, or rather unknown perhaps).

        I know that this is not satisfactory but have no better solution right now. Will talk to our cypher developers about it.

        Michael

        Comment


        • #5
          "The current approach is just to say, is there more data than just the current page"

          Yeah, but it isn't doing that either. I would love to just know if there is more data than the current page so I can put a next link on my list of data on my web page.

          And in the second query I ran where I am asking for the second page, it said it was the first page of two, instead of being the second page or two and that there is a previous page. Which would definitely be know.

          Thanks

          Mark

          Comment


          • #6
            Any news on this ?

            Maybe a dumb suggestion, but wouldn't it be possible to create a query with the limit = size + 1 and check if the number of elements returned is equals to size + 1 to know if there is gonna be a next page ?

            Mathieu

            Comment


            • #7
              Originally posted by dreamage View Post
              Any news on this ?

              Maybe a dumb suggestion, but wouldn't it be possible to create a query with the limit = size + 1 and check if the number of elements returned is equals to size + 1 to know if there is gonna be a next page ?

              Mathieu
              No matter what that would require 2 queries to run to do pagination. One for page information, counts, etc. and the second the actual real query.

              So as Michael said, it doesn't look like there will be anything done in the future, for now.

              So you have to write and do that first query your self to find about pagination, but you won't be able to modify the Page object to set the page information because it is immutable.

              Mark

              Comment


              • #8
                Yes this is a possible solution, I did something similar in another place but of course it is not the best solutions.

                And automatically constructing count queries is not so simple as they must not change the query structure (e.g. keep aggregation) but don't keep sorting (for performance reasons). Also the count query should only be executed once to get the information but the paged queries have to be executed several times.

                Comment


                • #9
                  Originally posted by MichaelHunger View Post
                  Also the count query should only be executed once to get the information but the paged queries have to be executed several times.
                  Yeah, where would you store that? and for how long? How do you know if you will even need to get the next page or any page after that first time running that query? So that would lean towards having to execute that count each time you query for a page.

                  Do any of the other Spring Data projects actually have pagination working? It seems that all DBs would have the same issue.

                  Mark

                  Comment


                  • #10
                    Good question about the other SD projects, I know that SD mongodb has the option of providing a separate count query but I didn't look into their implementation for retrieving a single page. Should be easy to find out though.

                    Pagination is tricky anyway with those large datasets and after all you want to refrain from querying the same full data (especially if you need to iterate over all of it) time and again.

                    Michael

                    Comment


                    • #11
                      Hi,

                      has anyone solved this? Currently the paging functionality doesn't seem to be working at all, because it is not even possible to find out if there is a next page or not. Any suggestions on how to overcome this issue?

                      Thanks,
                      Radim

                      Comment


                      • #12
                        In this thread Michael has said why it doesn't work and what you would need to do to get something working in your app. So no one has implemented it. Basically you need to run two queries. One for the count, and one for the actual query. And if you only do the count query once, then what happens if someone inserts new rows while you are paging, so then therefore you would need to run those two queries for each page to keep it up to date, and that is an overhead.

                        Personally, I think it should be implemented with two queries that way and noted in the documentation that pagination does this. This is how Spring Data Commons says Pagination works.

                        Mark

                        Comment


                        • #13
                          I still think there is space for improvement. At least it would be good to have correct information whether there is next page available or not, this is a minimum which should work in my opinion.

                          What I don't understand is why you would need 2 queries to do the size+1 solution which Mathieu suggested. When doing the server request, it should request size + 1 and a custom implementation of the Page interface should be returned which would only contain the requested count, and the hasNextPage method implementation of the Page interface would return whether there is an additional record or not based on existence or non existence of the additional returned result. Is this doable?

                          Radim

                          Comment


                          • #14
                            "When doing the server request, it should request size + 1"

                            That sounds like a second count query to me.

                            Just think of it in terms of the database. If I ask for 0-20 records and the query could have returned 100. How does the db know that? It doesn't so in order to find out that it would have had 100 records, is a count query. Doing a size + 1 doesn't change that fact. I don't see it.

                            Mark

                            Comment


                            • #15
                              I think you are misunderstanding the size + 1. I don't think a second query is needed. All I am saying is that already the first query should be constructed so that if I request 5 records, the query should retrieve 6 records and then when processing the results, it should only return the first 5 records in the Page object, but if a sixth record was returned, it means that there is an additional page.

                              Radim

                              Comment

                              Working...
                              X