0

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.

6
  • You are subtracting the limit from the offset which would result in a negative offset
    – Eric
    Apr 25, 2017 at 12:53
  • The error message is clear enough, OffsetSize is negative. You haven't provided enough context for anyone to be able to determine why exactly this is, however (where are totalrec and LimitSize defined? how is totalrecNJ different? is there a difference between Totalrec and totalrec?). Have you considered using a StandardSetController instance inside your current controller/extension? It would provide you with pagination controls (instead of rolling your own).
    – Derek F
    Apr 25, 2017 at 12:59
  • @DerekF limit has to be greater than offset. Can't have a limit of 10 offset 30 right?
    – Eric
    Apr 25, 2017 at 13:11
  • @Eric LIMIT 10 OFFSET 30 sounds perfectly valid to me (page size is 10, 4th page)
    – Derek F
    Apr 25, 2017 at 13:28
  • @DerekF guess I need to review offset again. Never use it. Thanks for the clarification.
    – Eric
    Apr 25, 2017 at 13:32

1 Answer 1

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.

2
  • Such a nice explaination very much impressed.But I'm afraid of changing the pagination to standardsetontroller as it is implmented in many other pages and classes.But no other way I guess,will try
    – Eagerin Sf
    Apr 27, 2017 at 6:39
  • can't edit the code at this stage.I tried inserting 11 records with the test data met with all the conditions set to true in the query in the test class but it didn't worked too
    – Eagerin Sf
    Apr 28, 2017 at 7:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.