Paginator Total Items with Joins and Querybuilder

Question

Paginator Total Items with Joins and Querybuilder

Trent Ramseyer Jun '17

Created Jun '17	Last Reply Jun '17	Replies 3	Views 884	Votes 0

Trent Ramseyer 22.7k

Jun '17

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?

elroliv · Answer 1 · 2017-06-27T01:15:39-07:00

elroliv
3.4k

Jun '17

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

Nikolay Mihaylov · Answer 2 · 2017-06-27T01:44:01-07:00

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)
]);

phil67000 · Answer 3 · 2017-06-27T02:07:01-07:00

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

but You can use


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

Trent Ramseyer · Answer 4 · 2017-06-27T11:37:34-07:00

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