Search code examples
phpyii2yii2-modelyii2-active-records

yii2 combine data from multiple queries in ActiveDataProvider

Am trying to combine data from different queries into a single active record for the purpose of pagination

I have two different databases with similar table, that is 

db1
  tbl_products
   id,quantity,price

Now on db2

db2
 tbl_products
  id,quantity,price

SO i have two models with different connections where first model connects to db1 and second to db2

class ProductsDb1 extends ActiveRecord{
   public static function getDb()
    {
      return 'db1'
    }
}

class ProductsDb2 extends ActiveRecord{
   public static function getDb()
    {
      return 'db2'
    }
}

SO now in my current query i have

$db1productsQuery = ProductsDb1::find()->where(...)
$db2productsQuery = ProductsDb2::find()->where(...);

On my ActiveDataProvider am passing my query like

     $data = new ActiveDataProvider([
          'query' => $db1productsQuery, //pass the dbproducts query
           'sort' => ['defaultOrder' => ['quantity' => SORT_DESC]],
            'pagination' => [
               'pageSize' => $arr['perPage'],
                  'page' => $arr['page']
              ],
            ]);

As from the above i have to create multiple activeDataProviders for each query and return data separately. Is it possible to combine or add both queries to a single ActiveDataProvider rather than having to create each data provider for each query

Solution

  • There are 3 options how to deal with this.

    1) UNION across two DB

    To do this, your both DBs must be on same server and your DB user must be granted privileges to access both DBs.

    $query = ProductsDb1::find()
    ->select(['id', 'quantity', 'price']);
$subquery = (new \yii\db\Query())
    ->select(['id', 'quantity', 'price'])
    ->from('secondDb.products');
$query->union($subquery, true);
$dp = new \yii\data\ActiveDataProvider([
    'query' => $query
]);

    2) ArrayDataProvider

    You can use \yii\data\ArrayDataProvider instead of \yii\data\ActiveDataProvider.

    $products1 = ProductsDb1::find()
    ->select(['id', 'quantity', 'price'])
    ->all();

$products2 = ProductsDb2::find()
    ->select(['id', 'quantity', 'price'])
    ->all();
$dp = new \yii\data\ArrayDataProvider([
    'allModels' => array_merge($products1, $products2),
])

    3) Implement your own data provider

    This option is most complicated. But if you dont match the limitation of first option and you have too many products to use second option you have to use this option. You can extend the \yii\data\ActiveDataProvider and override its prepareTotalCount() and prepareModels() methods to allow use of two queries.

    Resources