setFirstResult example

$builder->addOrderBy($sort);
        }

        $expr = $this->getManager()->getExpressionBuilder();
        $builder->andWhere(
            $expr->orX(
                $expr->eq($association['fieldName'] . '.id', ':id')
            )
        );
        $builder->setParameter('id', $id);

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

        $paginator = $this->getQueryPaginator($builder);

        $data = iterator_to_array($paginator);

        return [
            'success' => true,
            'data' => $data,
            'total' => $paginator->count(),
        ];
    }
if (!empty($ids)) {
            $ids = json_decode($ids, true);
            $this->addIdsCondition($builder$ids);
        } elseif (!empty($id)) {
            $this->addIdsCondition($builder[$id]);
        } else {
            if (!empty($term)) {
                $this->addSearchTermCondition($entity$builder$term);
            }

            $builder->setFirstResult($offset)
                ->setMaxResults($limit);
        }

        $pagination = $this->getPaginator($builder);
        $data = iterator_to_array($pagination);

        $data = $this->hydrateSearchResult($entity$data);

        $this->View()->assign([
            'success' => true,
            'data' => $data,
            
->leftJoin('product', 's_core_tax', 'tax', 'tax.id = product.taxID')
            ->leftJoin('product', 's_articles_img', 'img', 'img.articleID = product.id AND img.main = 1')
            ->leftJoin('img', 's_media', 'media', 'media.id = img.media_id')
            ->addSelect('SQL_CALC_FOUND_ROWS product.id as id')
            ->addSelect('product.name')
            ->addSelect('product.active')
            ->addSelect('ROUND(price.price*(100+tax.tax)/100,2) as price')
            ->addSelect('manual_sorting.position as position')
            ->addSelect('CONCAT("media/image/thumbnail/", img.img, "_140x140.", img.extension) as thumbnail')
            ->setParameter('categoryId', $categoryId)
            ->setMaxResults($limit)
            ->setFirstResult($start)
            ->addGroupBy('product.id');

        $data['data'] = $query->execute()->fetchAll();

        $data['data'] = array_map(function D$item) {
            $item['thumbnail'] = $this->mediaService->getUrl($item['thumbnail']);

            return $item;
        }$data['data']);

        $data['total'] = $query->getConnection()->fetchColumn('SELECT FOUND_ROWS()');

        

    public function getPropertyRelationQuery($filter = null, $order = null, $limit = null, $offset = null)
    {
        $builder = $this->getPropertyRelationQueryBuilder($filter$order);
        if ($offset !== null && $limit !== null) {
            $builder->setFirstResult($offset)
                   ->setMaxResults($limit);
        }

        return $builder->getQuery();
    }

    /** * Helper function to create the query builder for the "getPropertyRelationQuery" function. * This function can be hooked to modify the query builder of the query object. * * @param array|null $filter * @param string|OrderBy|null $order * * @return QueryBuilder */

    public function getFilterQuery($tokens$offset = null, $limit = null, $orderBy = null)
    {
        $builder = $this->getFilterQueryBuilder($tokens$orderBy);
        if ($offset) {
            $builder->setFirstResult($offset);
        }
        if ($limit) {
            $builder->setMaxResults($limit);
        }

        return $builder->getQuery();
    }

    /** * Returns the basic filter query builder, with all the rules (tokens) applied * * @param array $tokens * @param array $orderBy * * @return QueryBuilder */
'property' => 'groups.name',
                'value' => $query,
                'expression' => 'LIKE',
                'operator' => 'OR',
            ];
        }

        $builder->addFilter($filter);
        $builder->addOrderBy($request->getParam('sort', []));

        $builder
            ->setFirstResult($request->getParam('start'))
            ->setMaxResults($request->getParam('limit'));

        $query = $builder->getQuery();

        // Get total result of the query         $total = $this->get('models')->getQueryCount($query);

        // Select all shop as array         $data = $query->getArrayResult();

        // Return the data and total count

    protected function getListQuery($offset = 0, $limit = 25, array $criteria = [], array $orderBy = [])
    {
        /** @var QueryBuilder $builder */
        $builder = $this->getManager()->createQueryBuilder();
        $builder->select(['translation'])
            ->from(TranslationModel::class, 'translation')
            ->join('translation.shop', 'shop');

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

        if (!empty($criteria)) {
            $builder->addFilter($criteria);
        }
        if (!empty($orderBy)) {
            $builder->addOrderBy($orderBy);
        }

        return $builder;
    }

    
public function getUrls(Context $context$limit = null, $offset = null)
    {
        $qb = $this->getBaseQuery()
            ->addSelect(['cat.id', 'cat.blog'])
            ->orderBy('ISNULL(cat.path)', 'DESC')
            ->addOrderBy('id', 'ASC')
            ->setParameter(':shop', $context->getShopId())
            ->andWhere('cat.shops IS NULL OR cat.shops LIKE :shopLike')
            ->setParameter(':shopLike', '%|' . $context->getShopId() . '|%');

        if ($limit !== null && $offset !== null) {
            $qb->setFirstResult($offset)
                ->setMaxResults($limit);
        }

        $result = $qb->execute()->fetchAll();

        if (!\count($result)) {
            return [];
        }

        return $this->router->generateList(
            array_map(
                

    public function getListQuery($order = null, $offset = null, $limit = null)
    {
        $builder = $this->getListQueryBuilder($order);
        if (!empty($offset)) {
            $builder->setFirstResult($offset);
        }
        if (!empty($limit)) {
            $builder->setMaxResults($limit);
        }

        return $builder->getQuery();
    }

    /** * Helper function to create the query builder for the "getListQuery" function. * This function can be hooked to modify the query builder of the query object. * * @param array<array{property: string, direction: string}>|null $order * * @return QueryBuilder */
/** * {@inheritdoc} */
    public function getUrls(Context $context$limit = null, $offset = null)
    {
        $qb = $this->getBaseQuery()
            ->addSelect(['blog.id AS blogID', 'cat.id AS catID'])
            ->setParameter(':shop', $context->getShopId());

        if ($limit !== null && $offset !== null) {
            $qb->setFirstResult($offset)
                ->setMaxResults($limit);
        }

        $result = $qb->execute()->fetchAll();

        if (!\count($result)) {
            return [];
        }

        return $this->router->generateList(
            array_map(
                

    public function getOrdersQuery($customerId$filter = null, $orderBy = null, $limit = null, $offset = null)
    {
        $builder = $this->getOrdersQueryBuilder($customerId$filter$orderBy);
        if ($limit !== null) {
            $builder->setFirstResult($offset)
                    ->setMaxResults($limit);
        }

        return $builder->getQuery();
    }

    /** * Helper function to create the query builder for the "getOrdersQuery" function. * This function can be hooked to modify the query builder of the query object. * * @param int $customerId * @param string|null $filter * @param array|null $orderBy * * @return QueryBuilder */
$builder = $this->connection->createQueryBuilder();

        $builder->select([
            'COUNT(search.searchterm) AS countRequests',
            'search.searchterm',
            'MAX(search.results) as countResults',
            'GROUP_CONCAT(DISTINCT shops.name SEPARATOR ", ") as shop',
        ])
            ->from('s_statistics_search', 'search')
            ->leftJoin('search', 's_core_shops', 'shops', 'search.shop_id = shops.id')
            ->groupBy('search.searchterm')
            ->setFirstResult($offset)
            ->setMaxResults($limit);

        if (!empty($sort)) {
            foreach ($sort as $condition) {
                $builder->addOrderBy(
                    $condition['property'],
                    $condition['direction']
                );
            }
        }
        if (!empty($shopIds)) {
            
/** * {@inheritdoc} */
    public function getUrls(Context $context$limit = null, $offset = null): array
    {
        $qb = $this->getBaseQuery()
            ->addSelect(['id'])
            ->setParameter(':shop', $context->getShopId())
            ->orderBy('id');

        if ($limit !== null && $offset !== null) {
            $qb->setFirstResult($offset)
                ->setMaxResults($limit);
        }

        $result = $qb->execute()->fetchAll();

        if (!\count($result)) {
            return [];
        }

        return $this->router->generateList(
            array_map(
                
->leftJoin('address.state', 'state')
            ->leftJoin('address.attribute', 'attribute');

        if (!empty($filterBy)) {
            $builder->addFilter($filterBy);
        }

        if (!empty($orderBy)) {
            $builder->addOrderBy($orderBy);
        }

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

        return $builder;
    }
}

    public function getBackupListQuery($offset$limit)
    {
        $builder = $this->getBackupListQueryBuilder();

        if ($offset !== null) {
            $builder->setFirstResult($offset);
            $builder->setMaxResults($limit);
        }

        return $builder->getQuery();
    }

    /** * Helper function to create the query builder for the "getBackupListQuery" function. * This function can be hooked to modify the query builder of the query object. * * @return QueryBuilder */
Home | Imprint | This part of the site doesn't use cookies.