pagination problem

Hey guys! I would like to make a simple page pagination, but now I have a following problem and really hope on some help here :)

I tried to see the documentation , how to create pagination, but there was only example using ActiveRecord class which I am not using.

This is my query to fetch some data from database, inside a model:




public function getCarLog($car_id){

		

		$query = new Query;

		$query	->select('car_blog_id, blog_title, car_blog.car_id, blog_text, blog_type,

						  car_blog.created, car_blog_album_id, blog_id, photo, users.id, username, avatar')  

				->from('car_blog')

				->where(['car_blog.car_id' => $car_id,])

				->leftJoin('car_blog_album', 'car_blog_album.blog_id = car_blog.car_blog_id')

				->leftJoin('users', 'users.id = car_blog.user_id')			

				->groupBy('car_blog_album.blog_id');

				

		

				

		$command = $query->createCommand();

		$data = $command->queryAll([\PDO::FETCH_OBJ]);

		$numberOfCarLogs = count($data);

		$this->numberOfCarLogs = $numberOfCarLogs;

		return $data;

	}



And this code is in the controller:




 public function actionIndex(){	

		 $userCarsModel = new UserCars();

		 $car_id = Yii::$app->request->get('car_id');

		 $carData = $userCarsModel->getCarDataByCarId($car_id);

		 

		 $car_log = new UserCarBlog();

		 

		 return $this->render('index', [

				'carData' => $carData,

				'car_logs' => $car_log->getCarLog($car_id),

		 ]);

}



So, how can i make a pagination? 2 hours searching in google just showed me how to create it with AR class, but as i said, i am not using it here.

At least give me some advices or something…please :)

You can use ArrayDataProvider or SqlDataProvider.

Using ArrayDataProvider you preload all models and when models are many, this could get much time.

Using SqlDataProvider, you could try to get (Sql)DataProvider from your function.




public function getCarLogDataProvider($car_id){

                

                $query = new Query;

                $query  ->select('COUNT(*)')  

                                ->from('car_blog')

                                ->where(['car_blog.car_id' => $car_id,])

                                ->leftJoin('car_blog_album', 'car_blog_album.blog_id = car_blog.car_blog_id')

                                ->leftJoin('users', 'users.id = car_blog.user_id')                      

                                ->groupBy('car_blog_album.blog_id');

                                

                $rawsql = query->prepare(Yii::$app->db->queryBuilder)->createCommand()->rawSql;


                $countRecord = $query->createCommand()->queryScalar();




                $query = new Query;

                $query  ->select('car_blog_id, blog_title, car_blog.car_id, blog_text, blog_type,

                                                  car_blog.created, car_blog_album_id, blog_id, photo, users.id, username, avatar')  

                                ->from('car_blog')

                                ->where(['car_blog.car_id' => $car_id,])

                                ->leftJoin('car_blog_album', 'car_blog_album.blog_id = car_blog.car_blog_id')

                                ->leftJoin('users', 'users.id = car_blog.user_id')                      

                                ->groupBy('car_blog_album.blog_id');

                                

                $rawsql = query->prepare(Yii::$app->db->queryBuilder)->createCommand()->rawSql;




$dataProvider = new SqlDataProvider([

    'sql' => $rawsql,

    'totalCount' => $countRecord,

    'pagination' => [

        'pageSize' => 20,

    ],

]); 

                               

               return $dataProvider;

        }



In controller:




 public function actionIndex(){ 

                 $userCarsModel = new UserCars();

                 $car_id = Yii::$app->request->get('car_id');

                 $dataProvider = $userCarsModel->getCarLogDataProvider($car_id);

                 

                 $car_log = new UserCarBlog();

                 

                 return $this->render('index', [

                                'carData' => $carData,

                                'dataProvider' => $dataProvider,

                 ]);

}



But in my humble opinion i think that best way to solve this problem is to put sql in a sql view and than creating a model from this sql view. So you could use an ActiveDataProvider to get data.

in your controller:




 public function actionIndex(){ 

     $userCarsModel = new UserCars();

     $car_id = Yii::$app->request->get('car_id');

     $carData = $userCarsModel->getCarDataByCarId($car_id);

     

     $query = new Query;

     $query->select('car_blog_id, blog_title, car_blog.car_id, blog_text, blog_type,

               car_blog.created, car_blog_album_id, blog_id, photo, users.id, username, avatar')  

          ->from('car_blog')

          ->where(['car_blog.car_id' => $car_id,])

          ->leftJoin('car_blog_album', 'car_blog_album.blog_id = car_blog.car_blog_id')

          ->leftJoin('users', 'users.id = car_blog.user_id')                      

          ->groupBy('car_blog_album.blog_id');


      $pages = new \yii\data\Pagination(['totalCount' => $query->count()]);

      $car_logs = $query->offset($pages->offset)

            ->limit($pages->limit)

            ->all();


     return $this->render('index', [

           'carData' => $carData,

           'car_logs' => $car_logs,

           'pages' => $pages,

     ]);

 }



view:




<?php foreach($car_logs as $car_log): ?>

<?= $car_log['blog_title'] ?>

and so on...

<?php endforeach;?>


<?= \yii\widgets\LinkPager::widget([

      'pagination' => $pages,

  ]);

?>



Thank you guys for the help!

shiyang, i think it is working using your solution. I will test it later again. Now the data comes as simple array, but how to get it as objects?

because, before i was using this line to get it as objects:




queryAll([\PDO::FETCH_OBJ]);



I am using the code which shiyang provided.

If you want to get it as objects, why not use AR?But for the performance of Web applications,I do not recommend you use it.

No, i dont want to use AR.

to retrieve data as object, in your code i tried:




$car_logs = $query->offset($pages->offset)

            ->limit($pages->limit)

            ->all([\PDO::FETCH_OBJ]); // i have added pdo::fetch obj to retrive data as object




but it didnt work, it threw me an error : "Call to a member function getQueryBuilder() on a non-object".

but before to retrive data as object, i used this:




$data = $command->queryAll([\PDO::FETCH_OBJ]);



Or with this code which you have provided it is not possible to get data as object? If it not possbible, then ok…let it be simple array :)

As matter of fact,I suggest you let it be a simple array…

But if you want the data as object.You can try:

in your controller:




public function actionIndedx(){ 

    $userCarsModel = new UserCars();

    $car_id = Yii::$app->request->get('car_id');

    $carData = $userCarsModel->getCarDataByCarId($car_id);


    $query = new Query;

    $query->select('car_blog_id, blog_title, car_blog.car_id, blog_text, blog_type,

           car_blog.created, car_blog_album_id, blog_id, photo, users.id, username, avatar')  

      ->from('car_blog')

      ->where('car_blog.car_id=:car_id', [':car_id' => $car_id]) //!!!!Note that I've changed

      ->leftJoin('car_blog_album', 'car_blog_album.blog_id = car_blog.car_blog_id')

      ->leftJoin('users', 'users.id = car_blog.user_id')                      

      ->groupBy('car_blog_album.blog_id');


    $pages = new \yii\data\Pagination(['totalCount' => $query->count()]);


    $result = Yii::$app->db->createCommand($query->createCommand()->getSql()." LIMIT :offset,:limit");

    $result->bindValue(':car_id', $car_id);

    $result->bindValue(':offset', $pages->page*$pages->pageSize);

    $result->bindValue(':limit', $pages->pageSize);

    $car_logs = $result->query();

    $car_logs->setFetchMode(\PDO::FETCH_OBJ);


    return $this->render('index', [

       'carData' => $carData,

       'car_logs' => $car_logs,

       'pages' => $pages,

    ]);

}



then, your view




<?php foreach($car_logs as $car_log): ?>

<?= $car_log->blog_title ?>

and so on...

<?php endforeach;?>


<?= \yii\widgets\LinkPager::widget([

      'pagination' => $pages,

  ]);

?>