Paginator Total Items with Joins and Querybuilder

Hello,

I am using

<?php

$sql = "Select tb.*, t.* from Models\ToyBox tb 
LEFT JOIN Models\Toys t on t.tb_id = tb.id" 

$query = $this->modelsManager->createQuery($sql);
$results = $query->execute();

 $paginator =  new \Phalcon\Paginator\Adapter\Model(
            array(
                "data" => $results,
                "limit" => $per_page,
                "page" => $page
            )
        );

          $results = $paginator->getPaginate();

        $records =
        [
            "per_page" => $per_page,
            "current" => $results->current,
            "first" => 1,
            "before" => $results->before,
            "next" => $results->next,
            "last" => $results->last,
            "total_pages" => $results->total_pages,
            "total_items" => $results->total_items,
        ];

But I am getting the total items of Toys (total rows I guess?) instead of the Toy Boxes .

Any way to cure that?



1.9k

can we see your two models to understand connection between objects ?

First of all, you are not using QueryBuilder, but simple queries. Second of all, you are using the wrong pagination adapter.

Here is an example from recent project of mine (most of the code is stripped to save you some reading time :)).

// QueryBuilder
$builder = $this->modelsManager->createBuilder();
$builder->columns([
    'cruise.id AS cruiseId',
    'cruise.duration AS cruiseDuration',
    'cruise18.title AS cruiseTitle',
    'cruise18.slug AS cruiseSlug',
    'MIN(prices.price) AS lowestPricePerPerson',
]);
$builder->from(['cruise' => 'Models\Cruises']);
$builder->leftJoin('Models\CruisesI18n', 'cruise18.foreign_key = cruise.id', 'cruise18');
// We care only about prices for 2A in a single cabin
$builder->leftJoin('Models\CruisePrices', 'prices.cruise_id = cruise.id AND prices.occupancy_adult = 2 AND prices.occupancy_child = 0 AND prices.occupancy_junior = 0', 'prices');
$builder->where('cruise18.is_active = 1');
$builder->andWhere('cruise18.lang = :lang:', ['lang' => $lang]);
// ...

// Order
$builder->orderBy('prices.price ASC');
$builder->groupBy('cruise.id'); // We are joining with multi result table for the where filters

// Paging
$paginator = new \Phalcon\Paginator\Adapter\QueryBuilder([
    'builder' => $builder,
    'limit'   => (int) ($params['limit'] ?? 10),
    'page'    => (int) ($params['page'] ?? 0)
]);


2.9k
edited Jun '17

Yes $results->total_items, gives you the total o selected rows

but You can use

$box = new Models\ToyBox;
$total = $box->getCount();

Hello, Sorry its using PHQL not QueryBuilder, and the multiple toys still bring back rows in QB as well. Will figure out how to tweek accordingly.

Thanks