Magento – Magento count pagination sizes

Your concept is slightly lacking. The query should be at least moved outside of the template into Block class.

Then in the same class you can add something like this to solve your pagination issue:

protected function _beforeToHtml()
{
    $blockName = $this->getToolbarBlockName();
    if (!$blockName) {
        $blockName = 'product_list_toolbar';
        $this->setToolbarBlockName($blockName);
        $this->getLayout()->createBlock(
            $this->_defaultToolbarBlock,
            $blockName,
            array(
                 'show_toolbar' => $this->getData('show_toolbar'),
                 'list_mode' => $this->getData('list_mode'),
                 '_current_limit' => $this->getData('limit')
            ))
            ->setChild(
                $blockName . '_pager',
                $this->getLayout()->createBlock('page/html_pager', $blockName . '_pager')
            );
    }

    return parent::_beforeToHtml();
}

Unless you make fun of reinventing the wheel you can also use this extension to create a listing of all products of your store along with couple of other useful listings:

https://github.com/tim-bezhashvyly/CustomListing

Ok so i had to changed the way i used to access from 2 tables to 1 table and it has resolved my issue but yes somehow i was able to manage to get pagination with two collection by merging them as below rather than using array_merge

$page= ($this->getRequest()->getParam('p'))? $this->getRequest()->getParam('p') : 1;

if($page == 1)
{
    foreach($collection_1 as $coll) {
        
        $collection_2->addItem($coll);
    }
    return $collection_2;
}
else{
    return $collection_2;   
}

But the above code made one issue persistent that is incorrect pagination count eg: 1 to 12 of 18, Though in frontend all of the values were displayed i.e 20 in total

In my collection_1 there were 2 values and in collection_2 there were 18 values so in first page rather than showing first 10 values as per the limiter it showed 12 as i was adding 2 items runtime in collection_2 but pagination took the original count of collection_2 i.e 18 because using addItem() adds the data to the _items and not _data in collection array

Class: Magento\Framework\Data\Collection

Function: public function addItem(\Magento\Framework\DataObject $item)

which i think was the issue for the wrong pagination count as well as in getting the totalnum in pager.php which was eventually returning the collection_2 original count i.e 18 i added page check in my code to get whether it was page 1 because looping in foreach was adding those 2 values from collection_1 on every page.

I had to change the way due to time constraint, In case in future if anybody reaches till this point and goes further than would request to post their solution here as well so it can help others.