How do you get the number of remaining records for a subset fetch?

Question

symfony

How do you get the number of remaining records for a subset fetch?
Share your experience.

For example, I do this.
Could it be possible to do it differently?

/**
 * @method SupplierJournal|null find($id, $lockMode = null, $lockVersion = null)
 * @method SupplierJournal|null findOneBy(array $criteria, array $orderBy = null)
 * @method SupplierJournal[]    findAll()
 * @method SupplierJournal[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class SupplierJournalRepository extends ServiceEntityRepository implements RepositoryInterface
{
    private int $last_count_query = 0;

    /**
     * SupplierJournalRepository constructor.
     * @param ManagerRegistry $registry
     */
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, SupplierJournal::class);
    }

    /**
     * @param int $supplier_id
     * @param int $count
     * @param int $offset
     * @return mixed
     */
    public function findBySupplierId(int $supplier_id, int $offset = 0, int $count = 100)
    {
        $qb = $this->createQueryBuilder('sj');
        $qb = $qb->andWhere("sj.supplier = :supplier_id")
            ->setParameter("supplier_id", $supplier_id);

        try {
            $this->last_count_query = $qb
                ->select("count(sj)")
                ->getQuery()
                ->enableResultCache(3600)
                ->getSingleScalarResult();
        } catch (NoResultException $e) {
            $this->last_count_query = 0;
        } catch (NonUniqueResultException $e) {
            $this->last_count_query = 0;
        }

        return $qb
            ->select("sj")
            ->orderBy('sj.datetime', 'ASC')
            ->setFirstResult($offset)
            ->setMaxResults($count)
            ->getQuery()
            ->getResult();
    }


    public function countLastQuery(): int
    {
        return $this->last_count_query;
    }
}

In the controller.

...
public function getActionList()
    {
        $journal = $this->journal_repository->findBySupplierId($this->account->getUserId());
        $count = $this->journal_repository->countLastQuery();
 ...

Thank you.

Answer 1

https://github.com/BabDev/Pagerfanta

use Pagerfanta\Pagerfanta;

class PaginatedCollection
{
    private int $page;
    private int $size;
    private int $total;
    private int $pages;
    private array $items;

    public function __construct(Pagerfanta $pagerfanta)
    {
        $this->page = $pagerfanta->getCurrentPage();
        $this->size = $pagerfanta->getMaxPerPage();
        $this->total = $pagerfanta->getNbResults();
        $this->pages = $pagerfanta->getNbPages();
        $this->items = iterator_to_array($pagerfanta);
    }

    public function getPage(): int
    {
        return $this->page;
    }

    public function getSize(): int
    {
        return $this->size;
    }

    public function getTotal(): int
    {
        return $this->total;
    }

    public function getPages(): int
    {
        return $this->pages;
    }

    public function getItems(): array
    {
        return $this->items;
    }
}

// Repository
public function findPaginatedBySupplierId(int $supplier_id)
{
    return new Pagerfanta(DoctrineORMAdapter($this->getSupplierIdQueryBuilder($supplier_id)));
}

private function getSupplierIdQueryBuilder(int $supplier_id)
{
    $qb = $this->createQueryBuilder('sj');
    $qb = $qb
        ->select("sj")
        ->orderBy('sj.datetime', 'ASC')
        ->andWhere("sj.supplier = :supplier_id")
        ->setParameter("supplier_id", $supplier_id);

    return $qb;
}

// Controller
public function action()
{
    // ...
    return $this->json($this->paginatedCollection($repository->findPaginatedBySupplierId($id), $request));
}

// AbstractController
protected function paginatedCollection(Pagerfanta $pagination, Request $request, int $size = 20): PaginatedCollection
{
    $pagination = $this->paginate($pagination, $request, $size);

    return new PaginatedCollection($pagination);
}

protected function paginate(Pagerfanta $pagination, Request $request, int $size = 20): Pagerfanta
{
    $pagination->setMaxPerPage($size);
    $pagination->setCurrentPage($request->query->getInt('page', 1));

    return $pagination;
}

{
    "page": 1,
    "size": 20,
    "total": 76,
    "pages": 4,
    "items": [...]
}