Here’s a simplified version of my tables…
users table blog_posts table
id | username id | user_id | create_time
--------------- ------------------------------------
1 | user 1 1 | 2 | 2011-03-01 14:06:55
2 | user 2 2 | 3 | 2011-03-01 17:15:41
3 | user 3 3 | 1 | 2011-05-23 10:49:32
4 | 2 | 2011-06-03 20:35:15
5 | 3 | 2011-06-03 10:52:47
I want to select the columns in the user table ordered by the latest posts created by that user.
So for example I want to get a result set like this…
id | username
---------------
2 | user 2 <--- This persons last blog post was at 2011-06-03 20:35:15 <-----
3 | user 3 <--- This persons last blog post was at 2011-06-03 10:52:47 <----- see how these are ordered
1 | user 1 <--- This persons last blog post was at 2011-05-23 10:49:32 <-----
This is part of my UsersController…
class UsersController extends Controller
{
...
public function actionIndex()
{
$model=new Users('search');// set the search scenario
$model->unsetAttributes(); // clear any default values
if(isset($_GET['Users']))
$model->attributes=$_GET['Users'];
$this->render('index',array(
'model'=>$model,
));
}
...
}
This is my index view…
<?php
<div class="search-form">
<p>You can search for friends using the 6 different fields below. Just type in your search term and hit 'Enter'</p>
<?php $this->renderPartial('_search',array(
'model'=>$model,
)); ?>
</div><!-- search-form -->
<?php $this->widget('zii.widgets.CListView', array(
'dataProvider'=>$model->search(), <------------------------------take note of this
'itemView'=>'_view',
)); ?>
This is part of my UsersModel…
class Users extends ActiveRecord
{
...
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'blogComments' => array(self::HAS_MANY, 'BlogComments', 'commenter_id'),
'blogPosts' => array(self::HAS_MANY, 'BlogPosts', 'user_id'),
'contacts' => array(self::HAS_MANY, 'Contacts', 'contact_id'),
);
}
...
public function search()<---------------------------------------this gets called in the view
{
// Warning: Please modify the following code to remove attributes that
// should not be searched.
$criteria=new CDbCriteria;
$criteria->compare('fname',$this->fname,true);
$criteria->compare('lname',$this->lname,true);
$criteria->compare('username',$this->username,true);
$criteria->compare('city',$this->city,true);
$criteria->compare('country',$this->country,true);
$criteria->with = array('blogPosts'=>array('limit'=>1)); <-- here's how i'm attempting to do
$criteria->order = 'blogPosts.update_time ASC'; <-- what I mentioned above
$criteria->params = array(':id'=>Yii::app()->user->id);
return new CActiveDataProvider(get_class($this), array(
'criteria'=>$criteria,
));
}
...
I’m getting the following error
CDbCommand::fetchAll() failed: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'blogPosts.update_time' in 'order clause'. The SQL statement executed was: SELECT `t`.`id` AS `t0_c0`, `t`.`fname` AS `t0_c1`, `t`.`lname` AS `t0_c2`, `t`.`username` AS `t0_c3`, `t`.`email` AS `t0_c4`, `t`.`current_status` AS `t0_c5`, `t`.`last_status_update` AS `t0_c6`, `t`.`image` AS `t0_c7`, `t`.`password` AS `t0_c8`, `t`.`create_time` AS `t0_c9`, `t`.`update_time` AS `t0_c10`, `t`.`banned` AS `t0_c11`, `t`.`city` AS `t0_c12`, `t`.`country` AS `t0_c13`, `t`.`about_me` AS `t0_c14`, `t`.`paid` AS `t0_c15`, `t`.`dob` AS `t0_c16`, `t`.`init_cb_transaction` AS `t0_c17`, `t`.`gender` AS `t0_c18` FROM `users` `t` WHERE (t.id!=:id) ORDER BY blogPosts.update_time LIMIT 10.
I can see why this fails… because the blogPosts join isn’t even in the query… But why isn’t it there?
Can someone help me figure this out please. I’m stuck!
Cheers
Tom
