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:
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;
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 estilost LEFT OUTER JOIN academias_estilosestilosacademias ON (estilosacademias.id_estilo=t.id_estilo) LEFT OUTER JOIN academiasacademias 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
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.
CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound. The SQL statement executed was: SELECT * FROM estilost WHERE id_estilo= :estilo
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.
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.