The List Of Users Who Has Written In A Determined Category

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'),








$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(



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(



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:

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?

yes, did you see this?