soql offset error for Pagination in Test Class

Question

My class

Public Integer OffsetSize = 0;
   Public Integer LimitSize= 10;
   public integer totalrec{get;set;}
// StandardController relates to task
    public HomePageStd(ApexPages.StandardController controller) { 
        RecertGrpLstNJ = [Select Type,AccountNumber,Name,BillingStreet,BillingState,BillingCity,BillingPostalCode from Account where BillingState != 'NJ' and Type ='SMGRP-Group' limit :limitsize offset:offsetsize];  
        totalrec = [Select count() from Account where  BillingState != 'NJ' and Type ='SMGRP-Group'];

    }
    Public void LastPage()
    {
        OffsetSize = totalrec - math.mod(totalrec,LimitSize);  
        if(OffsetSize == Totalrec)
        { 
            Offsetsize = Offsetsize- limitsize;
            RecertGrpLst = [Select Type,AccountNumber,Name,BillingStreet,BillingState,BillingCity,BillingPostalCode from Account where BillingState != 'NJ' and Type ='SMGRP-Group' limit :limitsize offset:offsetsize];       
        }
        else
        {
            RecertGrpLst = [Select Type,AccountNumber,Name,BillingStreet,BillingState,BillingCity,BillingPostalCode from Account where BillingState != 'NJ' and Type ='SMGRP-Group' limit :limitsize offset:offsetsize];
        }
    }

I tried calling the method in Test Class as SPage.LastPage(); But it is throwing error saying that

System.QueryException: SOQL offset must be a non-negative value

I tried inserting 10 accounts in the test class but the error repeats again.

Answer 1

Working out the root cause of your error

Like I said in the comments, the error you're getting is simple enough, and tells you exactly what the problem is. OffsetSize is negative when you run a query in lastPage().

Based on the code you've provided, there's only one way for OffsetSize to get negative. You aren't inserting appropriate test data. I'll go over how I reached this conclusion.

When debugging, it can be helpful to take some time, and mentally step through each line of code that gets executed. Let's start by assuming that you have called lastPage().

OffsetSize = totalrec - math.mod(totalrec,LimitSize);

This is the first line of code we execute. totalrec can never be less than 0 (assuming that you are correctly extracting totalrec, an integer, from the query you perform in your constructor).

With LimitSize set to 10, any number of records <= 10 will result in OffsetSize being 0 after this line. Thus, this line of code isn't your problem (at least, not alone).

If OffsetSize != totalrec, you'd enter the else block, where you would end up having a query with OFFSET 0 LIMIT 10, which is perfectly valid.

The only situation where you would enter the if block is when the queries in your constructor return 0 rows.

0 - math.mod(0,10)

gives us 0 as a result.

Since totalrec = 0 and OffsetSize = 0, we enter your if block, and then proceed to OffsetSize = OffsetSize - LimitSize, resulting in an OffsetSize of -10. You then run your query, and receive your error.

Thus, the root cause of your error is that your test is not inserting even a single Account having BillingState != 'NJ' and Type ='SMGRP-Group'.

Fixing things

The current logic you have in lastPage() is wrong, you could 'fix' your test so that it no longer fails, but the code you're testing would still have this issue. (As a side note, this is why it's important to write tests that cover more than just the "happy path" where all of your input is well-formed and within your expectations.)

You could fix your logic to properly compute the last offset, but honestly, I would take the chance to switch to using a StandardSetController instance, which provides pagination controls for you. Why re-invent the wheel? Here's a link to the documentation for StandardSetController

A basic example of using StandardSetController for pagination would be this:

public class HomePageStd{
    public ApexPages.StandardSetController stdSetCon {get; set;}
    
    public HomePageStd(ApexPages.StandardController controller) {
        // One of the constructors for StandardSetController takes a Database.QueryLocator
        // Using a query locator will restrict the number of records that are queried
        //   at any given time.
        // I'm breaking things onto separate lines for readability.
        stdSetCon = new ApexPages.StandardSetController(
            Database.getQueryLocator(
                [
                  SELECT Type,AccountNumber,Name,BillingStreet,BillingState,BillingCity,BillingPostalCode 
                  FROM Account 
                  WHERE BillingState != 'NJ' and Type ='SMGRP-Group'
                ]
            )
        );  

        // From here, we can set the page size
        stdSetCon.setPageSize(10);
    }
}

After that, adding pagination to a visualforce page is a simple affair:

<apex:outputPanel layout="block" style="text-align:center;">
    <apex:commandButton disabled="{!NOT(stdSetCon.hasPrevious)}" value="First" action="{!stdSetCon.first}" />
    <apex:commandButton disabled="{!NOT(stdSetCon.hasPrevious)}" value="Prev" action="{!stdSetCon.previous}" />
    <apex:outputText value="{!stdSetCon.PageNumber} />
    <apex:commandButton disabled="{!NOT(stdSetCon.hasNext)}" value="Next" action="{!stdSetCon.next}" />
    <apex:commandButton disabled="{!NOT(stdSetCon.hasNext)}" value="Last" action="{!stdSetCon.last}" />
</apex:outputPanel>

A big benefit of taking this approach is that you don't have to write your own pagination logic in Apex. By extension, that also means that you don't need to test these pagination controls.