How to do pagination when result is returned by two DAO queries ?

In my controller action I am executing two DAO queries that will return me results for two different conditions ( it is very complex problem, and I can not figure out better way to do it ).

Part of that code:

// count offers for the second therritory 

$count2 = ClubOfferCpv::countOffers($id, $second_territory);


// if we have some offers from second country, let's include them

if ($count2 > 0) 

{

    $pagination = new Pagination(['totalCount' => $count+$count2, 'pageSize' => $this->_pageSize]);

    

    $firstCountry = ClubOfferCpv::getOffers($id, $territory_id, $pagination);


    $secondCountry = ClubOfferCpv::getOffers($id, $second_territory, $pagination);

}

I am passing the total count of $count+$count 2 to pagination object. For example, $count may be 2, and $count2 may be 5, So totalCount is 7, and pageSize is 5. So I want to be able to display 5 results per page with pagination. The problem comes with the fact that I am executing 2 different queries. Each of them is expecting LIMIT and OFFSET provided by pagination object.

These are the queries:

$result = $db->cache(function ($db) use ($join, $id, $territory_id, $pagination) {


    return  $db->createCommand("SELECT coc.club_offer_id, coc.cpv_id, coc.type,

                                       co.id, co.club_id, co.title, co.details, co.link, co.reference,

                                       ct.club_id, ct.teritorije_id,

                                       c.id, c.news_users_id, c.contact,

                                       nu.ID, nu.cmpid, nu.website,

                                       nus.id, nus.news_user_id, nus.status,

                                       f.id, f.maticni_broj, f.skraceni_naziv 

               

                               FROM `club_offer_cpv` coc


                               JOIN `club_offer` co             ON coc.club_offer_id = co.id

                               JOIN `club_territories` ct       ON co.club_id        = ct.club_id

                               JOIN `club` c                    ON co.club_id        = c.id

                               JOIN `news_users` nu             ON c.news_users_id   = nu.ID

                               JOIN `news_users_services` nus   ON nu.ID             = nus.news_user_id

                               ".$join."


                               WHERE coc.cpv_id = :cpvid

                               AND ct.teritorije_id = :terrid

                               AND nus.status IN (4, 6)


                               GROUP BY coc.club_offer_id


                               ORDER BY coc.type


                               LIMIT ".$pagination->limit."


                               OFFSET ".$pagination->offset."


    ")->bindValues([":cpvid" => $id, ":terrid" => $territory_id,])->queryAll();


}, self::$duration);


return $result;

They are both the same except that ".$join." part of the query will be different, since they need to join different tables based on some conditions.

Obviously problems is that both queries need LIMIT and OFFSET and pagination is only one with one total count. Can I transcend this somehow ? Can I get results from both queries paginated like they are comming from one query ?

How the items are displayed?

If you need 5 items of each list ($firstCountry, $secondCountry) on the page, then you just need to choose the largest count.

$pagination = new Pagination(['totalCount' => max($count, $count2), 'pageSize' => $this->_pageSize]);

If you need some other quantity of items (for example: 2 from $firstCountry and 3 from $secondCountry), then you need to calculate limit/offset manually, but the $pagination will be the same (with max($count, $count2)). Then for the $firstCountry (by 2 items) you use $limit=2 and $offset=2*($page-1) ($page is the current page starting with 1: on the first page $offset=2*(1-1)=0, on the second page $offest=2*(2-1)=2 etc.), and the same for $secondCountry but with $limit=3 and $offset=3*($page-1).

If you need to display the $firstCountry items first and only then the $secondCountry items, then your totalCount will be the sum of $count+$count2, $limit is still equal to pageSize, but the offsets will be different: the one from $pagination is for $firstCountry and $offset=$pagination->offset-$count. But you will have problems on the page where the two lists should converge. And you should not execute $secondCountry query until it’s offset is greater then 0-$pageSize. And I don’t know how DB will take offset less then zero. So, I hope that this is not the way you are to display the list… ; )

I see, my way seems to be impossible.

Let’s say that I have to do two queries like this:

Select * FROM books_en WHERE type = 1;

and:

Select * FROM books_fr WHERE type = 1;

I want to display 10 results per page. Where results from first query comes first on the list, and results from second query comes after. It doesn’t matter how many results each query has returned, as long as 10 are displayed per page. BUT, output need to be something like this:

<h2> English books: </h2>


<div> English Book 1 </div>


<div> English Book 2 </div>

.

.

.


<h2> French books: </h2>


<div> French Book 1 </div>


<div> French Book 2 </div>


.

.

.

Paginate if 10+

Any ideas how this should be done ? I need to use DAO.

I did not get it…

Let’s asume you have 50+ item counts for each query. Saying “display 10 results per page” you mean 5 english and 5 french or 10 english on the first page?

If 5+5 then you just use max($count, $count2) and pageSize=5

If 10 english then this is the most complicated option. You won’t do it with one line of code.

10 English. I need to get English ones as long as there are any. After that I need to list French with separate heading. I know that this is complicated, I am trying for 2 days

Well, first of all, you don’t need to get any of french books, until you need it (on page 5 maybe…).

So, the first thing:

$pagination = new Pagination(['totalCount' => $count+$count2, 'pageSize' => $this->_pageSize]);

That is correct, we will use it.

Then you need a bit of optimization here:

    $firstCountry = ClubOfferCpv::getOffers($id, $territory_id, $pagination);

    if (sizeof($firstCountry) < $pagination->pageSize) {

        $secondCountry = ClubOfferCpv::getOffers($id, $second_territory, $pagination);

    }

If there are less items then the page size in the first list, only then you need to fetch french items.

And then I recommend to extend your getOffers() method so it could do global offset for the french list. On example: asume you have 18 items in the first list and 15 in the second one. Then you got 8 items and need only 2 from the second list. We are on the second page. Current $pagination->offset is 10, $pagination->limit is 10. The values you expect for french list are: offset is 0, limit is 2. So, if you pass $count (the number of items in the first list) to the second list’s getOffers(), you can calculate the proper values for offset and limit.

// ceil($count / $pageSize) = the page with mixed lists

// $count % $pageSize = the items of the first list on the page with mixed lists

$limit = (ceil($count / $pageSize) < $page) ? $pageSize : $pageSize - ($count % $pageSize);

$offset = (ceil($count / $pageSize) < $page) ? $pagination->offset - $count : 0;

Page 1: we get only 10 items from the first list. Second list is not queried;

Page 2:

(ceil(18 / 10) < 2) = (2 < 2) = false

$limit = 10 - (18 % 10) = 10 - 8 = 2

$offset = 0

Next page 3:

(ceil(18 / 10) < 3) = (2 < 3) = true

$limit = 10

$offset = 20 - 18 = 2

Looks like correct.

Lets try another values: $count = 50; $pageSize = 10;

Page 5: we’ve got 10 items in $firstCountry, (10 < 10) = false so no second list.

Page 6: 0 items if $firstCountry, (0 < 10) = true so we load second list. $pagination->offset = 50

(ceil(50 / 10) < 6) = (5 < 6) = true

$limit = 10

$offset = 50 - 50 = 0

It’s fine! We’ve just loaded 10 items from the second list.

Not that hard… ; )

http://dev.mysql.com/doc/refman/5.0/en/union.html

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html#union

http://www.yiiframework.com/doc-2.0/yii-db-query.html#union()-detail

If first query returns 8 results, and second 22, this will not work.

On first page I will get 8 + 2, that is fine. But on second I will get 8 + 10. Results from the first query would still be displayed.

the view:

<?php if ($queryOne): ?>

    <?= $this->render('_index', ['data' => $queryOne]) ?>

<?php endif ?>


<?php if ($queryTwo): ?>

    <?= $this->render('_index', ['data' => $queryTwo]) ?>

<?php endif ?>

Why? With the proper limit/offset you will query

SELECT ... LIMIT 10, 10

If there are only 8 items, then offet = 10 will return zero items.