get count with hasMany not working yii2

yii2 get count of records
yii2 hasmany
yii2 get data from database
yii2 activerecord count
find all count yii2
yii2 working with database
yii2 join
yii2 joinwith

I have CLub model (clubs) hasMany with User model like

Club n-n User

and I have UserClub model with columns: id, club_id, user_id, etc

In Club model

public function getCountUsers()
{
    return $this->hasMany(UserClub::className(), ['club_id'=>'id'])->count();
}

I wanna count all User on Club as code:

$query = Club::find()
    ->joinWith(['countUsers']);
    // ->with('countUsers');
    ->all();

so it is not working and throwing an error

Club has no relation named \"countUsers\"."

Because it isn't a relation as it does not return a model object or an array of model objects, instead you are using ->count() that makes it return a string that contains the total count for the user against the club.

If you are looking to get a count for the users against all the Clubs you can use the currently defined relation like $club->countUser see below.

$clubs=Club::find()->all();

foreach($clubs as $club){
    echo $club->countUser;
}

or change the relation to

public function getCountUser(){
    return $this->hasMany(UserClub::className(), ['club_id'=>'id']);
}

and use it like

$clubs=Club::find()->all();
foreach($clubs as $club){
    echo count($club->countUser);
}

or like below

$clubs=Club::find()->all();
foreach($clubs as $club){
    echo $club->getCountUser()->count();
}
EDIT

You are actually trying to transform the following query using ActiveRecord as far as I understood from the discussion.

SELECT clubs.id, count(user_clubs.id) as total 
FROM 
clubs 
left join user_clubs on clubs.id = user_clubs.club_id 
group by clubs.id

if that is correct you can use the following

Clubs::find ()
        ->alias ( 'c' )
        ->select ( [ new \yii\db\Expression ( 'c.[[id]], count(uc.[[id]]) as total' ) ] )
        ->leftJoin ( '{{%user_clubs}} uc' , 'uc.club_id=c.id' )
        ->groupBy ( 'c.id' )
        ->all ();

Note : You have to do one more thing you have to add a public property $total inside your Club model and add it to safe rules, because you are selecting the count as an alias total and until unless you define it inside the model the result set won't show you the count, so add the following inside the Club model.

public $total;

under rules

[[other fields...,'total'] , 'safe' ] ,
EDIT2

For some reason, I have a feeling that you are trying to count by specifying a relation instead of specifying the ->leftJoin () with the table user_clubs in the query.

If that is so then you have to change your relation getUserCount() you should better give a meaningful name that describes it. i would rename it to getClubUsers()

public function getClubUsers(){
    return $this->hasMany(UserClub::className(), ['club_id'=>'id']);
}

After this, you still have to declare a public property $total as I described before inside your Club model, and add it to safe rules.

Now you can write your query in the following way

Clubs::find ()
    ->alias ( 'c' )
    ->select ( [ new \yii\db\Expression ( 'c.[[id]], count(cu.[[id]]) as total' ) ] )
    ->joinWith( ['clubUsers cu'] )
    ->groupBy ( 'c.id' )
    ->all ();

How to get count in relation table in yii2 Activerecord, I have a need to find the related data count from a Active Record, I found a link from stackoverflow� Sponsor yiisoft/yii2 not work with hasMany() but in my case it only changes the total count in response headers, but not the amount of data returned.

You can do this with join, in my case i get users who have more than 0 referrals.

$users = User::find()->with('referrals')
            ->from(User::tableName() . ' t')
            ->join('left join',User::tableName().' r','r.Deeplink = t.ReferralID')
            ->select('t.*,count(r.ID) as ct')
            ->groupBy('t.ID')
            ->andFilterHaving(['>','ct',0])
            ->all();

Working with Databases: Active Record, You may override this method if the table is not named after this convention. Create a new query object by calling the yii\db\ActiveRecord::find() method;; Build the COUNT(*) FROM `customer` WHERE `status` = 1 $count = Customer ::find() of the related Active Record instances; if a relation is declared with hasOne(),� I render the GridView and get: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'bar.id' in 'on clause' The SQL being executed was: SELECT COUNT(*) FROM (SELECT DISTINCT `tbl_foo`.*

Hi your relation is correct check you error Club has no relation named \"countUsers\"."

Means you are calling a relation which not exist : change query like this, Relation name should be in Club Model

public function getCount(){
 return $this->hasMany(UserClub::className(), ['club_id'=>'id']);
}



$clubs=Club::find()->all();
foreach($clubs as $club){
    echo count($club->getCount);
}


$query = Club::find()
    ->joinWith(['count']);
    // ->with('countusers');
    ->all(); 

If you want count just do like this . Load the Club model .

$club_model =  Club::find()
$count = club_model->count;

get count in relation table in yii2 Activerecord and sort it � Issue , In yii 1 I use this in model to define a relation for this purpose: 'postCount' Code � Issues 459 � Pull requests 56 � Actions � Wiki � Security � Insights GitHub is home to over 50 million developers working together to host and review code, get count in relation table in yii2 Activerecord and sort it #6042. When in relation use orderBy and next get count from \\yii\\db\\ActiveQuery, it will be use subquery to select count, as fix may be rewrite orderBy by set empty array. What steps will reproduce the pr

How create statistical query in relation? � Issue #2179 � yiisoft/yii2 , get count in relation table in yii2 Activerecord and sort it #6042 see how does yii\db\ActiveQuery::joinWith() solves the problem of eager loading for function getCountlicense() { return $this->hasMany(License::className()� Because the hasMany returns a array, and the data column class can't handle it. This line counts the relations if we search or use hasMany and not just one record This comment has been minimized.

Fetching has many relationship data Laravel and using avg function , Fetching has many relationship data Laravel and using avg function - php. I am working with yii2 to make a basic website. I need to load a users But the problem arises when I try get the Videos that are related to a User. public function getUservideojunctions() { return Getting count from pivot table in laravel eloquent. CUBRID 9.3 or later: via yii\db\ActiveRecord (Note that due to a bug in the cubrid PDO extension, quoting of values will not work, so you need CUBRID 9.3 as the client as well as the server) Sphinx: via yii\sphinx\ActiveRecord, requires the yii2-sphinx extension; ElasticSearch: via yii\elasticsearch\ActiveRecord, requires the yii2-elasticsearch

Db active record, However, camelCase is not a requirement, Yii can work well with any other to return the number of *active* customers: $count = Customer::find() ->where([' status' Remember that hasMany() returns an [[yii\db\ActiveQuery]] object which � Since you are loading the model with the post, I guess you should show the loaded results, instead of trying to get the post again:

Comments
  • what do you want a count or a query to join with relation?
  • updated the answer see the EDIT2 section i think that is what you were asking hope it helps
  • I don't want to foreach for that query. Here is my Query SELECT clubs.id, count(user_clubs.id) as total FROM clubs left join user_clubs on clubs.id = user_clubs.club_id group by clubs.id how to use related on Yii2 like above query
  • without using foreach how are you going to display all the clubs? or maybe you are trying to say something else @VoTienHung
  • I just want to show count all users via club and get club info.
  • @VoTienHung see the edit section i just transformed the query to ActiveRecord
  • Thanks for responding, but can't count them via hasMany?
  • count method do not work with relation with hasMany.