Doctrine ORM Query Offsets and Limits Made Simple

Recently, I’ve been building a mobile app using Ionic with a lazy loading feature. Utilizing the infinite scroll directive, when the user swipes and hits the end of the list, in this case a list of accounts, the mobile app request hits an API route I built that returns a JSON response inside a CRM (customer relationship management) system I am also working on, loading more to the list. Basically, it’s pagination.

The CRM I am working on is built on top of the Symfony framework. Now, I know you might be thinking, well there are already great bundles that provide for pagination like KNP Paginator. While that’s true, and I’ve used these bundles in other applications, for this use case, it would be overkill. I just needed something where I could set my request parameters, limit and offset, to a GET request that would then return my JSON response for that set of account entities.

Obviously, one could write a simple DQL query for the offset and the limit. However, complex fetches with joins in Doctrine aren’t as reliable. Below is a quote from the Doctrine documentation.

Paginating Doctrine queries is not as simple as you might think in the beginning. If you have complex fetch-join scenarios with one-to-many or many-to-many associations using the “default” LIMIT functionality of database vendors is not sufficient to get the correct results.

Doctrine Paginator class to the rescue! If you are using Doctrine 2.2 or later in your project, you have access to this class. Since the query I am using contains numerous joins and associations, this class is necessary and provided an easy to use interface that gave me exactly what I needed.

Below is the code that makes the query to the database, then sends it to a private function that formats the results before sending it back to the API controller route which gives the JSON response or an exception.

    /**
     * @param int $offset
     * @param int $limit
     * @return array
     */
    public function getAccounts($offset = 0, $limit = 50)
    {
        $qb = $this->em
            ->getRepository('AccountBundle:Account')
            ->createQueryBuilder('account')
            ->select('account, contact, primaryPhone, primaryEmail, primaryAddress, picture')
            ->leftJoin('account.contact', 'contact')
            ->leftJoin('contact.phones', 'primaryPhone', 'primaryPhone.primary = true')
            ->leftJoin('contact.emails', 'primaryEmail', 'primaryEmail.primary = true')
            ->leftJoin('contact.addresses', 'primaryAddress', 'primaryAddress.primary = true')
            ->leftJoin('contact.picture', 'picture')
            ->orderBy('contact.lastName', 'ASC')
        ;

        $qb ->setFirstResult($offset)
            ->setMaxResults($limit);

        $paginator = new Paginator($query, $fetchJoinCollection = true);

        return $this->formatResults($paginator);
    }

Below is the Ionic/Angular controller function that gets called on infinite scroll. There is a scope variable that keeps track of the offset number, $scope.offset. Every time the loadMore function is called the $scope.offset variable is incremented by 50, to get the next group from the database, then merges it with the accounts already loaded in memory. The loadMore function also calls a method passing the two arguments of offset and limit from the account service that sends the actual request to the CRM and returns a promise.

        // Infinite Scroll
        $scope.loadMore = function() {
            $scope.offset = $scope.offset + 50;
            var request = AccountService.loadAllAccounts($scope.offset, 50);
            request.query().$promise.then(function(data) {
                var count = 0;
                angular.forEach(data, function(value, key) {
                    this.push(value);
                    count++;
                }, $scope.accounts);

                // If less than 50 records were sent back, disable infinite scroll
                if (count < 50) {
                    $scope.noMoreItemsAvailable = true;
                }
                $scope.$broadcast('scroll.infiniteScrollComplete');
            }).catch(function(err) {
                console.error(err);
            });
        };

Finally, here is the service method that returns the accounts from the CRM.

        this.loadAllAccounts = function(offset, limit) {
            return $resource(apiUrl + '/api/rest/' + apiVersion + '/accounts.json',
                {
                    offset:  offset,
                    limit:   limit
                },
                {
                    query: {
                        method: "GET",
                        isArray: true,
                        headers: { 'X-WSSE': wsseHeader(Auth.getUsername(), Auth.getKey()) }
                    }
                }
            );
        };

I hope you found this short tutorial helpful! Let me know in your comments below!