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 ?