Pagination in custom inquiry

Hello everybody,

today I want to share with everybody, and with myself code fragment, that allows to achieve pagination in custom inquiry pages. 

Take a look on the code below:

  public class YourGraphInquiry : PXGraph<YourGraphInquiry>
  {
    public PXCancel<YourDACFilter> Cancel;
        public PXFilter<YourDACFilter> Filter;
 
        [PXFilterable]
        public PXSelect<YourDACDetails,
            Where2<
                Where<YourDACDetails.SomeidEqual<Current<YourDACFilter.SomeID>>, 
                    Or<Current<YourDACFilter.SomeID>, IsNull>>,
                And<
                    Where<YourDACDetails.SomepartidEqual<Current<YourDACFilter.SomePartId>>, 
                        Or<Current<YourDACFilter.SomePartId>, IsNull>>>>> Details;
 
        public virtual IEnumerable details()
        {
            PXView cmd = new PXView(thistrue, Details.View.BqlSelect);
            var currentFilter = Filter.Current;
 
            var s = (currentFilter.PageNbr ?? 0) * (currentFilter.PageSize ?? 0);
            int startRow = s > 0 ? s : PXView.StartRow;
            int totalRows = 0;
            int maxRows = (currentFilter.PageSize ?? 0) == 0 ? PXView.MaximumRows : currentFilter.PageSize ?? 0;
            foreach (var result in cmd.Select(new[] { currentFilter }, nullPXView.Searches,
                PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRowmaxRowsref totalRows))
            {
                yield return result;
            }
            startRow = 0;
        }
        public YourGraphInquiry ()
        {
            Details.Cache.AllowInsert = false;
            Details.Cache.AllowDelete = false;
            Details.Cache.AllowUpdate = false;
        }
 
       
  }
    
    [System.SerializableAttribute()]
    public class YourDACFilter : PX.Data.IBqlTable
    {
 
        #region SomeID
        public abstract class SomeID : BqlInt.Field<SomeID>
        {
        }
 
        protected int? _SomeID;
 
        [PXDBInt()]
        [PXDefault()]
        [PXUIField(DisplayName = "Some", Visibility = PXUIVisibility.Visible)]
        [PXSelector(typeof(Search<CWSome.SomeID>),   new Type[]{ typeof(MVP.CWSome.SomeCD), typeof(MVP.CWSome.description), typeof(MVP.CWSome.SomeCD) }, SubstituteKey = typeof(MVP.CWSome.SomeCD))]
        public virtual int? SomeID
        {
            get { return this._SomeID; }
            set { this._SomeID = value; }
        }
 
        #endregion SomeID
 
        #region SomePartId
 
        public abstract class SomePartId : BqlInt.Field<SomePartId>
        {
        }
 
        protected int? _SomePartId;
 
        [PXDBInt()]
        [PXDefault()]
        [PXUIField(DisplayName = "Some Part", Visibility = PXUIVisibility.Visible)]
        [PXSelector(typeof(Search<CWSomePart.SomePartIdWhere<CWSomePart.SomeIDEqual<Current<YourDACFilter.SomeID>>>>), 
                    new Type[] { typeof(CWSomePart.SomePartCD), typeof(CWSomePart.SomePartFullCD) }, SubstituteKey = typeof(CWSomePart.SomePartCD))]
        public virtual int? SomePartId
        {
            get { return this._SomePartId; }
            set { this._SomePartId = value; }
        }
        #endregion SomePartId
 
        #region Page number
        public abstract class pageNbr : BqlInt.Field<pageNbr> { }
        [PXInt]
        [PXUIField(DisplayName = "Page Number")]
        [PXDefault(0)]
        public virtual int? PageNbr { getset; }
 
        #endregion Page number
 
        #region page size
 
        public abstract class pageSize : BqlInt.Field<pageSize> { }
        [PXInt]
        [PXUIField(DisplayName = "Page Size")]
        [PXDefault(3)]
        public virtual int? PageSize { getset; }
 
        #endregion page size
    }
}

Presented code has few important features:

  1. With this code you can achieve fragmentation of reading of your data. 
  2. In order to avoid returning everything, used structure of yield return. It's efficient because allows to return not all array, but elements of array one by one
  3. In the end of details method, you can see startRow = 0. If you wonder why, reason is simple, after all elements in db will be executed, that line of code will reset reading of pages to the begining. 
  4. cmd.Select allows you to read chunk of data from db, in a very similar manner to SelectWindowed. If you check generated sql, you'll see staff like this:

SELECT TOP (3) [YourDACDetails].[Column1], [YourDACDetails].[Column2], .....

5. But for second and other pages, generated sql will not be very efficient, but on UI Side you'll not notice it. For example, when I've set Page number = 3, and page size = 5, I've got following sql:

exec sp_executesql N'SELECT TOP (20) [YourDACDetails].[Column1], [YourDACDetails].[Column2], ..... which leads to a conclusion, that Acumatica framework on later stage just throws out redundant values.

Summary

After all of those details description you may wonder, what can be the case, when you may need pagination for custom inquiry. And the main scenario, where such pagination may be useful is web services synchronization. If you want to achieve pagination for some of your data, then simplest way would be usage of mentioned pattern.

No Comments

Add a Comment