I am trying to do quering and sorting for a couple tables that are all associated with each other in relations. So here is my scenario and I hope I explain this well. I thank everyone in advance for any help or insight you can give me with this.
So in simple terms what I want to do is create scopes and named scopes to basically filter down my queries. So my first query I want to do is collect all the project for a user and then filter those by the active status and then go one step further order them the one that has the most recent activity recorded in a recent_activity table.
The relations for the Project Model is below:
public function relations(){
return array(
'accounts' => array(self::BELONGS_TO, 'Accounts', 'accounts_id'),
'recentActivities' => array(self::HAS_MANY, 'RecentActivity', 'projects_id'),
'accountRelations' => array(self::HAS_MANY, 'AccountRelation', 'projects_id'),
);
}
My scopes that I have working fine is below:
public function scopes(){
return array(
'active'=>array(
'condition'=>'status = "Active"',
),
'archived'=>array(
'condition'=>'status = "Archived"',
),
);
}
Named Scope to Filter by Account ID
public function userProjects($accountID){
$this->getDbCriteria()->mergeWith(array(
'condition'=>'accounts_id ='.$accountID,
));
return $this;
}
I want to create a named scope called orderByRecentActivity and I have my beginning of the code here but this is where I’m totally stuck. I can’t figure out how to use the relations in here to actually make sense with my overall query.
public function orderByRecentActivity($order='DESC'){
$this->getDbCriteria()->mergeWith(array(
'recentActivities'=>array(
'select'=>false,
'order'=>'createdDate '.$order,
),
));
return $this;
}
In my controller I have an account ID passed into the method and so I want to now query to retrieve all the projects for that account filtered by active and ordered by the most recent active project.
$userOwnedProjects = Projects::model()->userProjects($userID)->active()->orderByRecentActivity()->findAll();
This doesn’t work however. I run into a copule different issues. One it doesn’t like the relation in the getDbCriteria filter and playing around with these relations I get a lot of ambiguous columns and even declaring the actual table/field doesn’t fix it. (ie, projects.account_id).
So at this juncture I’m totally confused where I should be going with this. If I was writing this in straight queries I think I could it pretty easily.
This would be my query that would do the same of what I’m trying to achieve:
SELECT DISTINCT projects.id, projects.accounts_id, projects.title FROM projects LEFT JOIN recent_activity ON recent_activity.projects_id = projects.id WHERE projects.accounts_id = 489 ORDER BY recent_activity.create_date DESC
Any suggestions/advice/help here would really be appreciated.