Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- A table:
- a_id = 1, name = yes, number = 123
- a_id = 2, name = no, number = 456
- a_id = 3, name = ok, number = 683
- B table:
- id = 1, a_id = 1, firstname = s
- id = 2, a_id = 1, firstname = y
- id = 3, a_id = 2, firstname = e
- id = 4, a_id = 2, firstname = x
- id = 5, a_id = 2, firstname = t
- id = 6, a_id = 3, firstname = r
- a_id = 1, name = yes, number = 123
- a_id = 1, name = s, number = null
- a_id = 1, name = y, number = null
- a_id = 2, name = no, number = 456
- a_id = 2, name = e, number = null
- a_id = 2, name = x, number = null
- a_id = 2, name = t, number = null
- a_id = 3, name = ok, number = 683
- a_id = 3, name = r, number = null
- SELECT `a_id`, `name`, `number` FROM `user` WHERE number != ''
- UNION ALL
- SELECT `a_id`, `firstname` as name , null as `number` FROM `customer`
- WHERE `firstname` != ''
- ORDER BY `a_id` ASC, name ASC
- $query1 = (new yiidbQuery())
- ->select("a_id, name, number")
- ->from('user')
- ->where(['!=', 'number', '']);
- $query2 = (new yiidbQuery())
- ->select("a_id, firstname as name , null as number")
- ->from('customer')
- ->where(['!=', 'firstname', '']);
- $unionQuery = (new yiidbQuery())
- ->from(['dummy_name' => $query1->union($query2)])
- ->orderBy(['a_id' => SORT_ASC, 'name' => SORT_ASC]);
- $provider = new ActiveDataProvider([
- 'query' => $unionQuery,
- 'pagination' => [
- 'pageSize' => 20,
- ],
- ]);
- $rows = $provider->getModels();
- $query1 = (new yiidbQuery())
- ->select("a_id, name, number")
- ->from('user')
- ->where(['!=', 'number', '']);
- $query2 = (new yiidbQuery())
- ->select("a_id, firstname as name , null as number")
- ->from('customer')
- ->where(['!=', 'firstname', '']);
- $query1->union($query2, false);//false is UNION, true is UNION ALL
- $sql = $query1->createCommand()->getRawSql();
- $sql .= ' ORDER BY id DESC';
- $query = User::findBySql($sql);
- $dataProvider = new ActiveDataProvider([
- 'query' => $query,
- ]);
- $queryHistory = CoinsHistory::find();
- $queryHistoryRecent = CoinsHistoryRecent::find();
- $query = (new ActiveQuery(CoinsHistory::className()))->from([
- 'union_history' => $queryHistory->union($queryHistoryRecent)
- ]);
- $dataProvider = new ActiveDataProvider([
- 'query' => $query,
- 'sort' => ['defaultOrder' => ['id' => SORT_DESC]]
- ]);
- $filter = [];
- $queryHistory->andFilterWhere($filter);
- $queryHistoryRecent->andFilterWhere($filter);
- // Query Table A
- $tableA = (new yiidbQuery())
- ->select("a_id, name, number")
- ->from('user')
- ->where(['!=', 'number', '']);
- // Query table B
- $tableB = (new yiidbQuery())
- ->select("a_id, firstname as name , null as number")
- ->from('customer')
- ->where(['!=', 'firstname', '']);
- // Union table A and B
- $tableA->union($tableB);
- /*
- * Table A is your Model
- * find() method in activeRecord will load the instance of ActiveQuery
- * Now you can use base Query methods like select and from on find() method
- */
- $query = TableA::find()->select('*')->from(['random_name' => $tableA]);
- // Dataprovider
- $dataProvider = new ActiveDataProvider([
- 'query' => $query,
- 'pagination' => [
- 'pageSize' => 20,
- ],
- 'sort'=> ['defaultOrder' => ['a_id' => SORT_ASC]],
- ]);
- //Search filters and grid filters can go here
- return $dataProvider;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement