When using Phalcon's modelsManager to construct complex queries involving multiple table joins and conditional filters, there appears to be a mismatch between the total number of items reported by directly querying the database versus using Phalcon's paginator. This discrepancy could potentially affect data presentation and user navigation in applications relying on accurate pagination.

use Phalcon\Mvc\Model\Query\Builder as QueryBuilder;

// Setup the Query Builder
$builder = $this->modelsManager->createBuilder()
->columns('DISTINCT MainTable.id')
->from(['MainTable' => 'MainEntity'])
->where("MainTable.owner_id = :owner_id: AND is_active = 1", ['owner_id' => $owner_id])
->innerJoin('RelatedEntity1', 'MainTable.owner_id = RelatedEntity1.id')
->innerJoin('RelatedEntity2', 'RelatedEntity2.id = MainTable.related_id')
->leftJoin('OptionalEntity1', 'OptionalEntity1.related_id = RelatedEntity2.id')
->leftJoin('OptionalEntity2', 'OptionalEntity2.related_id = RelatedEntity2.id')
->leftJoin('OptionalEntity3', 'OptionalEntity3.main_id = MainTable.id')
->orderBy('MainTable.id DESC');

// Apply filter if present
$filter = $this->request->get('search', 'string');
if (!empty($filter)) {
$conditions = "MainTable.id = :filterId: OR OptionalEntity3.custom_field LIKE :filterDomain: OR OptionalEntity1.name LIKE :filterName:";
$builder->andWhere($conditions, [
'filterId' => $filter,
'filterDomain' => '%' . $filter . '%',
'filterName' => '%' . $filter . '%',
]);
}

// Initialize Paginator
$paginator = new QueryBuilder([
'builder' => $builder,
'limit' => 13,
'page' => $currentPage
]);

// Direct total count
$totalItems = $builder->getQuery()->execute()->count();

// Paginator total count
$page = $paginator->paginate();
$paginatorTotalItems = $page->getTotalItems();

// Output for debugging
echo "Direct count: $totalItems
";
echo "Paginator count: $paginatorTotalItems
";

Details
Version: 5.6.1-2+020240213.7+debian101.gbp9d5d41
php 8.1