Ok, I need a query on the database design:
CATEGORIES (id_category) <------------ POSTCATEGORY (id_category, id_post) -----------> POSTS (id_post, id_user) -----------> USER (id_user)
I want to do: The list of users who has written in a determined category.
For example, if I get the category "Sports" my query should show:
Michael (4 posts) -> Michael has written 4 posts in this category
David (3 posts)
Anna (1 posts)
Any ideas? Thanks!
The way is similar to previous (posts count in each category):
use model:
class User extends CActiveRecord {
public function relations() {
return array(
'posts'=>array(self::HAS_MANY, 'Post', 'id_user'),
'postcategories'=>array(self::HAS_MANY, 'Postcategory', 'id_post', 'through'=>'posts'),
);
}
...
}
controller:
$users = User::model()->with('postcategories')->findAll(array(
'select'=>'*, count(posts.id_post) AS posts_count',
'condition' => 'postcategories.id_category = :id_category',
'group' => 'posts.id_post'),
'params' => array(':id_category'=>$id_category),
));
foreach ($users as $user) {
echo $user->posts_count;
}
Hi MadAnd, it’s seems not working. Is possible use through with HAS_MANY?? The example of the documentation uses HAS_ONE only.
Trough is definitely supports HAS_MANY as well as HAS_ONE (btw HAS_ONE is just a special case of HAS_MANY). Also now i see that there is logical mistake in controller query (it looks like that my brain was too tired in the end of working day). The proper query should be:
$users = User::model()->with('postcategories')->findAll(array(
'select'=>'*, count(posts.id_user) AS posts_count',
'condition' => 'postcategories.id_category = :id_category',
'group' => 'posts.id_user'),
'params' => array(':id_category'=>$id_category),
));
Although the ActiveRecord gives you some level of abstraction against SQL, but only for common simple tasks. That’s why every web-developer must understand SQL well to be able to solve such issues as subj.
I try this:
$users = User::model()->with('posts')->findAll(array(
'select'=>'*',
));
print_r ($users)
And shows all columns of Users and Posts (id_user, id_post, etc…)
But I try:
$users = User::model()->with('postcategories')->findAll(array(
'select'=>'*',
));
print_r ($users)
And shows Users, Posts fields (id_user, id_post…) but there isn’t fields relative to Postcategory table. And this is a problem because I can’t do a query “postcategories.id_category = :id_category’,”
The relations seems ok, any idea?
Ok, according to thie post: http://www.yiiframework.com/forum/index.php/topic/38050-whats-the-difference-with-with/
I need to put:
$users = User::model()->with('posts.postcategories')->findAll(array(........
to work fine (it seems to do it right).
Note “posts.postcategories” and not only “postcategories”. I don’t know why.
Do you mean that for example
print_r ($users[0]->postcategories)
is return empty array?