yii2 combine data from multiple queries in ActiveDataProvider

Question

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

Answer 1

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

• \yii\db\Query::union()
• \yii\data\ArrayDataProvider

Answer 2

There is one more option how to unite data for yii2 dataprovider. Using MySql VIEWS.

If two databases are located on the same mysql server, then it is possible to create one view for two tables from different databases.

CREATE VIEW union_products AS 
SELECT db1.id, db1.quantity, db1.price FROM db1.tbl_products as db1 
UNION 
SELECT db2.id, db2.quantity, db2.price FROM db2.tbl_products as db2

You can add aliases to column names like db1.id as db1_id etc. Then create Active Record model with name matching the view name:

class UnionProducts extends \yii\db\ActiveRecord
{
    public static function getDb()
    {
        // using connection "db2"
        return \Yii::$app->db2;  
    }

    public static function tableName()
    {
       return 'union_products';
    }

And make a selection as from a separate table...

Remember that if you create a view inside the first database, then this connection must be used to access the view. You can create the same views in both databases.

For more information about MySql VIEWS please read https://dev.mysql.com/doc/refman/8.0/en/views.html

To show all views in mysql server run sql:

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM information_schema.tables 
WHERE TABLE_TYPE LIKE 'VIEW';

If needed delete view:

DROP VIEW IF EXISTS db1.your_view