Is This Query Possible With Relations?

Yii 1.1 General Discussion
1

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!

2

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

					));
3

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

	)

);
4

Thanks for the tips MadAnd!

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

5

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;
6

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.

7

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

8

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:

[b]Estilos/b <------------ AcademiasEstilos(id_estilo, id_academia) -----------> Academias(id_academia, id_poblacion) -----------> [b]Poblacion/b

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

9

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

10

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

	));
11

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.

12

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

13

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

        ));
14

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

));
15

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!

16

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)