Yii2 custom paginaion for Union query

问题:

I want to implement custom pagination in Yii2. this is my code

$connection = Yii::$app->getDb();
$name       =   $_GET['name'];
$query      =   '
                SELECT name FROM user WHERE name LIKE "%.'$name'.%"" 
            UNION
                SELECT name FROM user2 WHERE name LIKE "%.'$name'.%"" 
            UNION
                SELECT name FROM user3 WHERE name LIKE "%.'$name'.%"" 
            UNION
                SELECT name FROM user4 WHERE name LIKE "%.'$name'.%"" 
            ';              
$result      = $connection->createCommand($query)->queryAll();

Please let me know how can i implement pagination in Yii2

回答1:

Plz try this

$totalCount =   0;

$name       = $_GET['name'];
$connection = Yii::$app->getDb();
$limit      =   10;

$from       =   (isset($_GET['page'])) ? ($_GET['page']-1)*$limit : 0; // Match according to your query string

$sql        =   '
            SELECT name FROM user WHERE name LIKE "%.'$name'.%"" 
        UNION
            SELECT name FROM user2 WHERE name LIKE "%.'$name'.%"" 
        UNION
            SELECT name FROM user3 WHERE name LIKE "%.'$name'.%"" 
        UNION
            SELECT name FROM user4 WHERE name LIKE "%.'$name'.%"" 
        ';
$command = $connection->createCommand($sql.' LIMIT '.$from.','.$limit);

$count   = $connection->createCommand('SELECT COUNT(*) as total FROM ('.$sql.') a')->queryAll();

$result         = $command->queryAll();

$totalCount     =   $count[0]['total'];

$pages = new Pagination(['totalCount' => $totalCount, 'pageSize' => $limit]);       

回答2:

Here's a more proper Yii2 way:

In Controller:

    $page = Yii::$app->request->get('page', 1);
    $limit = Yii::$app->request->get('per-page', 10);
    $from = ($page-1)*$limit; 

    //Get the db connection
    $db = new yii\db\Connection(Yii::$app->db);

    //prepare SQL query
    $sql = '
            SELECT name FROM user WHERE name LIKE "%.'$name'.%"" 
        UNION
            SELECT name FROM user2 WHERE name LIKE "%.'$name'.%"" 
        UNION
            SELECT name FROM user3 WHERE name LIKE "%.'$name'.%"" 
        UNION
            SELECT name FROM user4 WHERE name LIKE "%.'$name'.%"" 
        ';

    //Fire it!
    $result =   $db->createCommand($sql.' LIMIT '.$from.','.$limit)->queryAll();
    $count   = $db->createCommand('SELECT COUNT(*) as total FROM ('.$sql.') a')->->queryScalar();

    $pagination = new \yii\data\Pagination(['totalCount' => $count, 'pageSize' => $limit]);             
    return $this->render('your-view', [
        'result' => $result,
        'pagination' => $pagination,
    ]);

Then anywhere in Your View:

echo \yii\widgets\LinkPager::widget([
    'pagination' => $pagination,
]);

I myself got help from E-Avni Tech's answer. If my answer helps, please upvote him.