[kitune]

Hi! This is my database:

CATEGORIES (id:_category) <------------ POSTCATEGORY (id_category, id_post) -----------> POSTS (id_post, id_user) -----------> USER (id_user)

Ok, I want to do this query: The list of categories in which a user has written, sorted by the number of posts. Sorry for my english...

For example, if I clic in the user David, it shows:

sports (10 posts)
travel (2 posts)
fun (1 post)

Is this possible using relations? What's the vest way to do this?

Thanks!

[kitune]

First attempt, results seems ok for now, but I want to know your opinion.

In Categories Model:

'post_category'=>array(self::HAS_MANY, 'PostCategory', 'id_category'),
'posts'=>array(self::HAS_MANY, 'Posts', 'id_post', 'through'=>'post_category'),

In controller:

$categories_user=Categories::model()->with('posts')->findAll(array(
					'select'=>'*',					
					'condition'=> 'posts.id_user='.$user->id_user,
					'order' => 'count(t.id_category) desc',					
					'group' => 't.id_category',				
					));

[MadAnd]

Hi kitune,

you have properly defined relations in Categories Model, but i have two notices about the controller code:

• 'select'=>'*' - is redundant, since it's the default for CDbCriteria
• 'condition'=> 'posts.id_user='.$user->id_user - usage of such inline substitutions could potentially expose SQL-injections, so the preffered way is to use PDO's parameters binding. In you case the code might look like:

$categories_user=Categories::model()->with('posts')->findAll(array(
		'condition'=> 'posts.id_user = :id_user',
		'order' => 'count(t.id_category) desc',
		'group' => 't.id_category'),
	array(
		':id_user'=>$user->id_user
	)
);

[kitune]

MadAnd, on 19 November 2012 - 09:48 AM, said:

Hi kitune,

you have properly defined relations in Categories Model, but i have two notices about the controller code:

• 'select'=>'*' - is redundant, since it's the default for CDbCriteria
• 'condition'=> 'posts.id_user='.$user->id_user - usage of such inline substitutions could potentially expose SQL-injections, so the preffered way is to use PDO's parameters binding. In you case the code might look like:

$categories_user=Categories::model()->with('posts')->findAll(array(
		'condition'=> 'posts.id_user = :id_user',
		'order' => 'count(t.id_category) desc',
		'group' => 't.id_category'),
	array(
		':id_user'=>$user->id_user
	)
);

Thanks for the tips MadAnd!

And one more thing, how can I show the number of posts of each category for the related user?

[MadAnd]

Please try the following solution:

in you Categories Model file add a new property:

class Categories extends CActiveRecord {
	
	public $related_user_posts_count;
	
	
	...
	
}

in the controller code now should look like:

$categories_user=Categories::model()->with('posts')->findAll(array(
		'select'=>'*, count(t.id_category) AS related_user_posts_count',
		'condition'=> 'posts.id_user = :id_user',
		'params'=>array(':id_user'=>$user->id_user),
		'order' => 'count(t.id_category) desc',
		'group' => 't.id_category'),
));

//now you will be able to acceess posts count like:
echo $categories_user[0]->related_user_posts_count;

This post has been edited by MadAnd: 27 November 2012 - 10:24 AM

[kitune]

MadAnd, on 19 November 2012 - 09:48 AM, said:

Hi kitune,

you have properly defined relations in Categories Model, but i have two notices about the controller code:

• 'select'=>'*' - is redundant, since it's the default for CDbCriteria
• 'condition'=> 'posts.id_user='.$user->id_user - usage of such inline substitutions could potentially expose SQL-injections, so the preffered way is to use PDO's parameters binding. In you case the code might look like:

$categories_user=Categories::model()->with('posts')->findAll(array(
		'condition'=> 'posts.id_user = :id_user',
		'order' => 'count(t.id_category) desc',
		'group' => 't.id_category'),
	array(
		':id_user'=>$user->id_user
	)
);

Sorry but I try this way and fails:

"CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound. The SQL statement executed was....."

I don't know why.

[MadAnd]

Please provide the SQL query itself (i.e. the text after "The SQL statement executed was.....").

[kitune]

MadAnd, on 19 November 2012 - 10:18 AM, said:

Please provide the SQL query itself (i.e. the text after "The SQL statement executed was.....").

Ok, but my tables aren't exactly named as the first post. I changue the name of te tables for better explain, but the funcionality are the same.

My tables:

Estilos(id_estilo) <------------ AcademiasEstilos(id_estilo, id_academia) -----------> Academias(id_academia, id_poblacion) -----------> Poblacion(id_poblacion)

My Estilos model:

'estilosacademias'=>array(self::HAS_MANY, 'AcademiasEstilos', 'id_estilo'),
'academias'=>array(self::HAS_MANY, 'Academias', 'id_academia', 'through'=>'estilosacademias'),

My Controller:

$estilos_empresas=Estilos::model()->with('academias')->findAll(
		array(										
			'condition'=> 'academias.id_poblacion= :id_poblacion',
			'order' => 'count(t.id_estilo) desc',					
			'group' => 't.id_estilo',										
		),
		array(
			':id_poblacion'=> $poblacion->id_poblacion	
		));

And the error:

CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound. The SQL statement executed was: SELECT `t`.`id_estilo` AS `t0_c0`, `t`.`nombre` AS `t0_c1`, `t`.`slug` AS `t0_c2`, `academias`.`id_academia` AS `t1_c0`, `academias`.`nombre` AS `t1_c1`, `academias`.`slug` AS `t1_c2`, `academias`.`direccion` AS `t1_c3`, `academias`.`latitud` AS `t1_c4`, `academias`.`longitud` AS `t1_c5`, `academias`.`id_poblacion` AS `t1_c6`, `academias`.`id_provincia` AS `t1_c7`, `academias`.`id_usuario` AS `t1_c8` FROM `estilos` `t` LEFT OUTER JOIN `academias_estilos` `estilosacademias` ON (`estilosacademias`.`id_estilo`=`t`.`id_estilo`) LEFT OUTER JOIN `academias` `academias` ON (`estilosacademias`.`id_academia`=`academias`.`id_academia`) WHERE (academias.id_poblacion= :id_poblacion) GROUP BY t.id_estilo ORDER BY count(t.id_estilo) desc

[MadAnd]

Please ensure that $poblacion->id_poblacion has non-NULL value, because it look like :id_poblacion was set to NULL.

[kitune]

MadAnd, on 19 November 2012 - 10:52 AM, said:

Please ensure that $poblacion->id_poblacion has non-NULL value, because it look like :id_poblacion was set to NULL.

Yes, this works fine:

$estilos_empresas=Estilos::model()->with('academias')->findAll(
	array(							
		'condition'=> 'academias.id_poblacion= '.$poblacion->id_poblacion,
		'order' => 'count(t.id_estilo) desc',					
		'group' => 't.id_estilo',										
	));

[MadAnd]

Sorry, but in this case i have no further idea why the error happens.

You could try to enable CWebLogRoute, while setting enableParamLogging of the DB connection to TRUE. This way you'll be able to see what value is actually is being bind for :id_poblacion.

[kitune]

MadAnd, on 19 November 2012 - 11:33 AM, said:

Sorry, but in this case i have no further idea why the error happens.

You could try to enable CWebLogRoute, while setting enableParamLogging of the DB connection to TRUE. This way you'll be able to see what value is actually is being bind for :id_poblacion.

It's strange, I reduced to the max the query finding errors:

$estilos_empresas=Estilos::model()->findAll(
	array(									
		'condition'=> 'id_estilo= :estilo',																
	),
	array(
		':estilo'=> '3',	
	));

And this fails too! The sintax is ok?

CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound. The SQL statement executed was: SELECT * FROM `estilos` `t` WHERE id_estilo= :estilo

[kitune]

And this works! An alternate sintax in 'params'

$estilos_empresas=Estilos::model()->with('academias')->findAll(
					array(								
						'condition'=> 'academias.id_poblacion= :id_poblacion',
						'order' => 'count(t.id_estilo) desc',					
						'group' => 't.id_estilo',	
						'params' => array(':id_poblacion'=> $poblacion->id_poblacion)									
					));

Why the first simplified versión doesn't works??

$estilos_empresas=Estilos::model()->findAll(
        array(                                                                  
                'condition'=> 'id_estilo= :estilo',                                                                                                                             
        ),
        array(
                ':estilo'=> '3',        
        ));

[MadAnd]

Ah sorry , that was my mistake. Now i got it.

In my examples I'm passing "params" as the second parameter to findAll(), but should pass them as a parameter for CDbCriteria, that will be created if the first parameter is array. Here is description which i've forgotten about.

The proper solution is the following:

$categories_user=Categories::model()->with('posts')->findAll(array(
	'condition' => 'posts.id_user = :id_user',
	'order' => 'count(t.id_category) desc',
	'group' => 't.id_category'),
	'params' => array(':id_user'=>$user->id_user),
));

[kitune]

MadAnd, on 19 November 2012 - 11:57 AM, said:

Ah sorry , that was my mistake. Now i got it.

In my examples I'm passing "params" as the second parameter to findAll(), but should pass them as a parameter for CDbCriteria, that will be created if the first parameter is array. Here is description which i've forgotten about.

The proper solution is the following:

$categories_user=Categories::model()->with('posts')->findAll(array(
	'condition' => 'posts.id_user = :id_user',
	'order' => 'count(t.id_category) desc',
	'group' => 't.id_category'),
	'params' => array(':id_user'=>$user->id_user),
));

Ok, this is the key I think:

parameters to be bound to an SQL statement. This is only used when the first parameter is a string (query condition). In other cases, please use CDbCriteria::params to set parameters.

In my case, the first parameter is an array.

Thanks a lot for all the help!

[kitune]

Ok, now I need the reversed query on the same 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)