Best way to paginate related data?

jonah · February 21, 2009, 6:17am

I have a user page which shows his posts using pagination. I was wondering what the best way to do this is? This is what I have come up with, but it's not as elegant as it could be

<?php


	public function actionShow() {


		$user = $this->loadUser(isset($_GET['id']) ? $_GET['id'] : Yii::app()->user->id);


		


		$criteria=new CDbCriteria;


		$criteria->condition = '`post`.`user_id`=''.$user->id.''';


		$pages=new CPagination(Post::model()->count($criteria));


		$pages->pageSize=4;


		$pages->applyLimit($criteria);


		$user->post = Post::model()->with('parsecache')->findAll($criteria);


		


		$this->render('show', compact('user', 'pages'));


	}

Would it be possible to get it all the needed results with one query (and a JOIN)?

This is creating a redundant query also (the last one):



SELECT * FROM `user` WHERE `user`.`id`=1 LIMIT 1





SELECT COUNT(*) FROM `post` WHERE `post`.`user_id`='1'





SELECT `post`.`id` AS t0_c0, `post`.`user_id` AS t0_c1, `post`.`title` AS t0_c2, `post`.`content` AS t0_c3, `post`.`created` AS t0_c4, `post`.`modified` AS t0_c5 FROM `post` WHERE `post`.`user_id`='1' LIMIT 4





SELECT `post`.`id` AS t0_c0, t1.`table` AS t1_c0, t1.`id` AS t1_c1, t1.`column` AS t1_c2, t1.`content` AS t1_c3 FROM `post` LEFT OUTER JOIN `parsecache` t1 ON t1.`id`=`post`.`id` AND `table`='post' WHERE `post`.`id` IN (5, 6, 63, 71)

And it should not need IN (5, 6, 63, 71) since it already has the ON condition t1.id=post.id

jonah · February 21, 2009, 6:24am

BTW it would be optimal if you could pass a CDbCriteria class into the with() function i think.

qiang · February 21, 2009, 12:40pm

You can use the following query (requires the latest SVN code) to avoid the redundant query.



Post::model()->with('parsecache')->together()->findAll($criteria);

What is the relation type of parsecache? If it is HAS_MANY or MANY_MANY, the query will be split into two without 'together()'.

How do you want to use CDbCriteria in with()? Currently you can use the following:



Post::model()->with(array(


    'parsecache'=>array(name-value pairs),


))->together()->findAll($criteria);

jonah · February 22, 2009, 1:23am

that helps, but it's still not working well.

<?php


	public function actionShow() {


			


		$criteria=new CDbCriteria;


		$pages=new CPagination(Post::model()->count($criteria));


		$pages->pageSize=4;


		$pages->applyLimit($criteria);


		


		$id = isset($_GET['id']) ? $_GET['id'] : Yii::app()->user->id;


		$user = User::model()->with(array('post'=>array('with'=>'parsecache'), 'parsecache'))->together()->findbyPk($id, $criteria);


		


		if (!$user)


			throw new CHttpException(404,'The requested user does not exist.');


			


		$this->render('show', compact('user', 'pages'));


	}

It correctly joins the posts to the user, and the parsecache to the to the user, but it does not join the parsecache to the posts

Quote

SELECT `user`.`id` AS t0_c0, `user`.`username` AS t0_c1, `user`.`password` AS t0_c2, `user`.`email` AS t0_c3, `user`.`email_visible` AS t0_c4, `user`.`notify_comments` AS t0_c5, `user`.`notify_messages` AS t0_c6, `user`.`about` AS t0_c7, `user`.`group_id` AS t0_c8, `user`.`email_confirmed` AS t0_c9, `user`.`created` AS t0_c10, `user`.`modified` AS t0_c11, t1.`id` AS t1_c0, t1.`user_id` AS t1_c1, t1.`title` AS t1_c2, t1.`content` AS t1_c3, t1.`created` AS t1_c4, t1.`modified` AS t1_c5, t2.`table` AS t2_c0, t2.`id` AS t2_c1, t2.`column` AS t2_c2, t2.`content` AS t2_c3 FROM `user` LEFT OUTER JOIN `post` t1 ON t1.`user_id`=`user`.`id` LEFT OUTER JOIN `parsecache` t2 ON t2.`id`=`user`.`id` AND `table`='user' WHERE `user`.`id`=1 LIMIT 4

SELECT * FROM parsecache WHERE table='post' AND id=5 AND column='content' LIMIT 1

SELECT * FROM parsecache WHERE table='post' AND id=6 AND column='content' LIMIT 1

SELECT * FROM parsecache WHERE table='post' AND id=63 AND column='content' LIMIT 1

SELECT * FROM parsecache WHERE table='post' AND id=71 AND column='content' LIMIT 1

it should retrieve user, join posts and parsecache to it, and then join parsecache to the posts. That's three joins

qiang · February 22, 2009, 1:36am

Use this:



User::model()->with('post', 'post.parsecache', 'parsecache')->...

jonah · February 22, 2009, 1:49am

Ok, so now it looks like this:

$user = User::model()->with('post', 'post.parsecache', 'parsecache')->together()->findbyPk($id, $criteria);

Which produces the following joins:



SELECT `user`.`id` AS t0_c0, `user`.`username` AS t0_c1, `user`.`password` AS t0_c2, `user`.`email` AS t0_c3, `user`.`email_visible` AS t0_c4, `user`.`notify_comments` AS t0_c5, `user`.`notify_messages` AS t0_c6, `user`.`about` AS t0_c7, `user`.`group_id` AS t0_c8, `user`.`email_confirmed` AS t0_c9, `user`.`created` AS t0_c10, `user`.`modified` AS t0_c11, t1.`id` AS t1_c0, t1.`user_id` AS t1_c1, t1.`title` AS t1_c2, t1.`content` AS t1_c3, t1.`created` AS t1_c4, t1.`modified` AS t1_c5, t2.`id` AS t2_c0, t2.`user_id` AS t2_c1, t2.`title` AS t2_c2, t2.`content` AS t2_c3, t2.`created` AS t2_c4, t2.`modified` AS t2_c5, t3.`table` AS t3_c0, t3.`id` AS t3_c1, t3.`column` AS t3_c2, t3.`content` AS t3_c3, t4.`table` AS t4_c0, t4.`id` AS t4_c1, t4.`column` AS t4_c2, t4.`content` AS t4_c3 FROM `user`





LEFT OUTER JOIN `post` t1 ON t1.`user_id`=`user`.`id`


LEFT OUTER JOIN `post` t2 ON t2.`user_id`=`user`.`id`


LEFT OUTER JOIN `parsecache` t3 ON t3.`id`=t2.`id` AND `table`='post'


LEFT OUTER JOIN `parsecache` t4 ON t4.`id`=`user`.`id` AND `table`='user'


WHERE `user`.`id`=1 LIMIT 4

Also an error:

Quote

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'table' in on clause is ambiguous

qiang · February 22, 2009, 1:57am

You need to disambiguate the reference to table column. Prepend them with ??.

jonah · February 22, 2009, 9:47pm

Like this?

<?php





$user = User::model()->with('??.post', 'post.parsecache', '??.parsecache')->together()->findbyPk($id, $criteria

);

That throws an error:

Relation "??" is not defined in active record class "User".

here's my code:

<?php


	public function actionShow() {


		//$user = $this->loadUser(isset($_GET['id']) ? $_GET['id'] : Yii::app()->user->id);


		$id = isset($_GET['id']) ? $_GET['id'] : Yii::app()->user->id;


			


		$criteria=new CDbCriteria;


		$pages=new CPagination(Post::model()->count($criteria));


		$pages->pageSize=4;


		$pages->applyLimit($criteria);





		$user = User::model()->with('post', 'post.parsecache', 'parsecache')->together()->findbyPk($id, $criteria);


		


		if (!$user)


			throw new CHttpException(404,'The requested user does not exist.');


			


		$this->render('show', compact('user', 'pages'));


	}

qiang · February 22, 2009, 9:49pm

Nope. It's in your relation declaration about the 'on' option. You should use ??.table='post'

jonah · February 22, 2009, 9:56pm

like this?

<?php


	/**


	 * Returns relation needed for model


	 */


	public function parseCacheRelation() {


		return array(CActiveRecord::HAS_MANY, 'ParseCache', 'id', 'on'=>'??.table=''.$this->Owner->tableName().''');	


	}

It's not replacing the ??. with anything though, thus throwing a mysql error.

qiang · February 22, 2009, 9:59pm

I see. You need to explicitly special 'alias' option and then prefix the column with that alias.

jonah · February 22, 2009, 10:05pm

<?php


	/**


	 * Returns relation needed for model


	 */


	public function parseCacheRelation() {


		return array(


			CActiveRecord::HAS_MANY, 'ParseCache', 'id',


			'on'=>'`pcaa123`.`table`=''.$this->Owner->tableName().''',


			'alias'=>'pcaa123'


		);	


	}

Error:

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'pcaa123'

sql:

query with SQL: SELECT user.id AS t0_c0, user.username AS t0_c1, user.password AS t0_c2, user.email AS t0_c3, user.email_visible AS t0_c4, user.notify_comments AS t0_c5, user.notify_messages AS t0_c6, user.about AS t0_c7, user.group_id AS t0_c8, user.email_confirmed AS t0_c9, user.created AS t0_c10, user.modified AS t0_c11, t1.id AS t1_c0, t1.user_id AS t1_c1, t1.title AS t1_c2, t1.content AS t1_c3, t1.created AS t1_c4, t1.modified AS t1_c5, t2.id AS t2_c0, t2.user_id AS t2_c1, t2.title AS t2_c2, t2.content AS t2_c3, t2.created AS t2_c4, t2.modified AS t2_c5, pcaa123.table AS t3_c0, pcaa123.id AS t3_c1, pcaa123.column AS t3_c2, pcaa123.content AS t3_c3, pcaa123.table AS t4_c0, pcaa123.id AS t4_c1, pcaa123.column AS t4_c2, pcaa123.content AS t4_c3 FROM user

LEFT OUTER JOIN post t1 ON t1.user_id=user.id

LEFT OUTER JOIN post t2 ON t2.user_id=user.id

LEFT OUTER JOIN parsecache pcaa123 ON pcaa123.id=t2.id AND pcaa123.table='post'

LEFT OUTER JOIN parsecache pcaa123 ON pcaa123.id=user.id AND pcaa123.table='user'

WHERE user.id=1 LIMIT 4

Edit

oops, gave you the wrong error & sql. fixed

qiang · February 22, 2009, 10:11pm

hmm, you are using the same alias for parsecache. It should be different…

jonah · February 22, 2009, 10:14pm

Sorry, I gave you the wrong sql output. it's fixed now

edit: btw not sure what you mean…

qiang · February 22, 2009, 10:18pm

Is it a behavior? You probably can differentiate the alias for different owner class.

qiang · February 22, 2009, 10:22pm

Try this:



<?php


   /**


    * Returns relation needed for model


    */


   public function parseCacheRelation() {


      $alias=get_class($this->owner).'_pcaa123';


      return array(


         CActiveRecord::HAS_MANY, 'ParseCache', 'id',


         'on'=>$alias.'.`table`=''.$this->Owner->tableName().''',


         'alias'=>$alias


      );   


   }

jonah · February 22, 2009, 10:23pm

Yes, it's from a behavior.

<?php


	/**


	 * Returns relation needed for model


	 */


	public function parseCacheRelation() {


		return array(


			CActiveRecord::HAS_MANY, 'ParseCache', 'id',


			'on'=>'`table`=''.$this->Owner->tableName().'''


		);	


	}

table is always column in parsecache, not a column in the model that the behavior is attached to. So I would think this would work:

<?php


	/**


	 * Returns relation needed for model


	 */


	public function parseCacheRelation() {


		return array(


			CActiveRecord::HAS_MANY, 'ParseCache', 'id',


			'on'=>'`parsecache`.`table`=''.$this->Owner->tableName().'''


		);	


	}

but i get this:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'parsecache.table' in 'on clause'

jonah · February 22, 2009, 10:24pm

Woa.

I changed the code to this:

<?php


	/**


	 * Returns relation needed for model


	 */


	public function parseCacheRelation() {


		return array(


			CActiveRecord::HAS_MANY, 'ParseCache', 'id',


			'condition'=>'??.`table`=''.$this->Owner->tableName().''',


		);	


	}

and everything works fine. Is that what I'm supposed to do?

jonah · February 22, 2009, 10:38pm

Nevermind, it's not working perfectly. It always pulls the first post (plus three more correct ones) no matter what page it is on, and the number of pages it thinks there is is wrong.

jonah · February 22, 2009, 10:47pm

I was wrong, it should be 'on', not 'condition'. But it's still not working