Count total by criteria while searching with limit and offset – how to?

Question

Count total by criteria while searching with limit and offset – how to?

Roman Mar '16

Created Mar '16	Last Reply Mar '16	Replies 3	Views 1424	Votes 0

Roman 21.7k

Mar '16

Hi!

I have two models (Cars and Drivers) with many-to-one relation (drivers can drive many cars, but car has only one driver-owner). Phalcon version is 2.0.10

How to search "every car that drives John"?

Now i'm trying to do this (see all failed attempts in code):

<?php

namespace API\Controllers;

use API\Models;

class Cars extends Base
{
    public function search()
    {
        $criteria = Models\Cars::query()
            ->join(Models\Drivers::class, Models\Cars::class . '.driver_id = ' . Models\Drivers::class . '.id')
            ->where(
                Models\Drivers::class . '.username LIKE :driver_username:',
                ['driver_username' => '%' . $this->request->get('driver', 'string') . '%']
            )
        ;

        // ===== TRY #1 =====
        // I set limit before executing query
        $criteria->limit(100, 30);
        $cars = $criteria->execute();
        // [FAIL] 100 – it'll show any number that i set in 'limit' method of  criteria
        echo "Cars found: " . $cars->count() . PHP_EOL;
        // [ OK ] Only 100 cars – as expected
        $this->response->send(json_encode($cars->toArray()));

        // ===== TRY #2 =====
        // Without limit
        $cars = $criteria->execute();
        // [ OK ] 342 – Works like a charm
        echo "Cars found: " . $cars->count() . PHP_EOL;
        // [FAIL] All 342 cars – i don't need them, only 100 )-:
        $this->response->send(json_encode($cars->toArray()));

        // ===== TRY #3 =====
        // Using model
        $criteria->limit(100, 30);
        $cars = Models\Cars::find($criteria);
        // [FAIL] 100500 – WAT?! Why it returns ALL cars?! (O_O)
        echo "Cars found: " . $cars->count() . PHP_EOL;
        // [FAIL] All 100500 cars – i don't need them! They're not searched.
        $this->response->send(json_encode($cars->toArray()));

        // ===== TRY #4 =====
        // Using criteria counter
        // [FAIL] Fatal error: Call to undefined method Phalcon\Mvc\Model\Criteria::count()
        $cars = $criteria->count();
    }
}

So, my question is simple now: how to get data with limit/offset but count all with search?

Thanks.

David Hübner · Answer 1 · 2016-03-29T06:37:09-07:00

Simply, you cant do it in one query, you have to place 2 - first for count(*) without limit and second with limit to return results.

Roman · Answer 2 · 2016-03-29T07:12:19-07:00

Simply, you cant do it in one query, you have to place 2 - first for count(*) without limit and second with limit to return results.

Yep, i know that i need here two queries – okay, i can do this.

But i need to send two identical queries: one for counting without limit and second with limit to get data.

Problem is that i cannot reuse created $criteria for these queries. Do i need to duplicate $criteriaData and $criteriaCounter? Even if their conditions are same? That's not DRY )-:

David Hübner · Answer 3 · 2016-03-29T09:28:59-07:00

This should work for you


namespace API\Controllers;

use API\Models;

class Cars extends Base
{
    public function search()
    {
        $criteria = Models\Cars::query()
            ->join(Models\Drivers::class, Models\Cars::class . '.driver_id = ' . Models\Drivers::class . '.id')
            ->where(
                Models\Drivers::class . '.username LIKE :driver_username:',
                ['driver_username' => '%' . $this->request->get('driver', 'string') . '%']
            );

        $cnt = $criteria->columns('COUNT(*) AS cnt')->execute();
        $result = $criteria->columns('*')->limit(100, 30)->execute();
        ...
    }
}

Dylan Anderson · Answer 4 · 2016-03-29T10:45:36-07:00

If you use a Paginator + Query Builder rather than limit/offset, you can get the count from the Paginator. I believe the Paginator uses SQL_CALC_ROWS rather than doing 2 full, expensive queries.