Option to query multiple times when result set exceeds 1000

[rmawatson]

It would be great to have an option for all queries to perform multiple queries to quickbooks when the count() > 1000 rather then having to do the multiple queries myself with start_position.

I currently have this hacked in to replace the query method on the QuickBooks class.

Great work btw!

[rmawatson]

This is what I am currently using, and appears to work fine for me with Deposit and Purchase so far. I would be great also for everything that can multi-request to take a cb argument such that progress can notified as it chunks through.

import quickbooks.mixins

def _fixed_where(cls, where_clause="", order_by="", start_position="", max_results="", qb=None,cb=None):
    """
    :param where_clause: QBO SQL where clause (DO NOT include 'WHERE')
    :param order_by:
    :param start_position:
    :param max_results:
    :param qb:
    :return: Returns list filtered by input where_clause
    """

    if where_clause:
        where_clause = "WHERE " + where_clause

    if order_by:
        order_by = " ORDERBY " + order_by

    if start_position != "":
        start_position_= int(start_position)
        start_position_query = " STARTPOSITION " + str(start_position)
    else:
        start_position = 0
        start_position_query = ""

    max_chunk = 1000
    single_query = False
    if max_results and int(max_results) <= max_chunk:
        max_results_query = " MAXRESULTS " + str(max_results)
        single_query = True
    else:
        max_results_query = " MAXRESULTS " + str(max_chunk)

    select = "SELECT * FROM {0} {1}{2}{3}{4}".format(
        cls.qbo_object_name, where_clause, order_by, start_position_query, max_results_query)
        
    result = cls.query(select, qb=qb)

    if single_query:
        return result          

    total_results = cls.count(where_clause,qb) - start_position

    if max_results:
        total_results = min(int(max_results),total_results)
    
    if total_results == max_chunk:
        return result

    for position in range(max_chunk+1,total_results,max_chunk):
        
        start_position_query = " STARTPOSITION " + str(start_position + position)
        max_results_query = " MAXRESULTS " + str(max_chunk)

        select = "SELECT * FROM {0} {1}{2}{3}{4}".format(
            cls.qbo_object_name, where_clause, order_by, start_position_query, max_results_query)
            
        result += cls.query(select, qb=qb)

    return result

quickbooks.mixins.ListMixin.where = classmethod(_fixed_where)

[bdsoha]

I am doing something similar, but without changing the base code:

def paginate(client, repo, per_page, method_name='all', order_by='Id', **kwargs):
    # Can be any QBO object i.e. `Customer.count`
    total = repo.count(qb=client)

    pages = total // per_page

    method  = getattr(repo, method_name)

    callback = partial(method, qb=client, max_results=per_page, order_by=order_by, **kwargs)

    objects = [
        callback(start_position=page * per_page + 1)
        for page in range(pages + 1)
    ]

    return list(reduce(lambda prev, curr: prev + curr, objects, []))

[bdsoha]

Another more robust alternative:

class Paginator(object):
    def __init__(self, repo, per_page, qb=None):
        self._repo = repo
        self._per_page = per_page
        self._client = qb

    def all(self, order_by="", start_position=0):
        total = self._repo.count(qb=self._client)

        pages = total // self._per_page

        callback = partial(self._repo.all, qb=self._client, max_results=self._per_page, order_by=order_by)

        objects = [
            callback(start_position=start_position + page * self._per_page + 1)
            for page in range(pages + 1)
        ]
        
        return list(reduce(lambda prev, curr: prev + curr, objects, []))

    def filter(self, order_by="", start_position="", **kwargs):
        ...

    def choose(self, choices, field="Id"):
        ...

    def where(self, where_clause="", order_by="", start_position=""):
        ...


class ListMixin(object):
    ...
    
    @classmethod
    def paginate(cls, per_page=100, qb=None):
        return Paginator(cls, per_page, qb)

[ej2]

There are some great examples here, but I think this is beyond the scope of this library.