Yii Framework Forum: Is This Query Possible With Relations? - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Is This Query Possible With Relations? Rate Topic: -----

#1 User is offline   kitune 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 121
  • Joined: 27-February 11

Posted 19 November 2012 - 07:23 AM

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!
0

#2 User is offline   kitune 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 121
  • Joined: 27-February 11

Posted 19 November 2012 - 07:53 AM

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

0

#3 User is offline   MadAnd 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 109
  • Joined: 16-June 11
  • Location:Ukraine

Posted 19 November 2012 - 09:48 AM

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



0

#4 User is offline   kitune 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 121
  • Joined: 27-February 11

Posted 19 November 2012 - 09:56 AM

View PostMadAnd, 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?
0

#5 User is offline   MadAnd 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 109
  • Joined: 16-June 11
  • Location:Ukraine

Posted 19 November 2012 - 10:09 AM

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

2

#6 User is offline   kitune 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 121
  • Joined: 27-February 11

Posted 19 November 2012 - 10:15 AM

View PostMadAnd, 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.
0

#7 User is offline   MadAnd 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 109
  • Joined: 16-June 11
  • Location:Ukraine

Posted 19 November 2012 - 10:18 AM

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

#8 User is offline   kitune 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 121
  • Joined: 27-February 11

Posted 19 November 2012 - 10:35 AM

View PostMadAnd, 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
0

#9 User is offline   MadAnd 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 109
  • Joined: 16-June 11
  • Location:Ukraine

Posted 19 November 2012 - 10:52 AM

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

#10 User is offline   kitune 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 121
  • Joined: 27-February 11

Posted 19 November 2012 - 11:06 AM

View PostMadAnd, 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',										
	));

0

#11 User is offline   MadAnd 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 109
  • Joined: 16-June 11
  • Location:Ukraine

Posted 19 November 2012 - 11:33 AM

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.
0

#12 User is offline   kitune 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 121
  • Joined: 27-February 11

Posted 19 November 2012 - 11:47 AM

View PostMadAnd, 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
0

#13 User is offline   kitune 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 121
  • Joined: 27-February 11

Posted 19 November 2012 - 11:54 AM

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

0

#14 User is offline   MadAnd 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 109
  • Joined: 16-June 11
  • Location:Ukraine

Posted 19 November 2012 - 11:57 AM

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

2

#15 User is offline   kitune 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 121
  • Joined: 27-February 11

Posted 19 November 2012 - 12:02 PM

View PostMadAnd, 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!
0

#16 User is offline   kitune 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 121
  • Joined: 27-February 11

Posted 27 November 2012 - 10:02 AM

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

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users