CODEWITHSUNDEEP
phpmysqlyii
Luciano Nascimento
Luciano Nascimento

Reputation: 2600

Count Relational Table Rows

I'm trying to count the number of rows in a relational table many to many, but always returns the wrong value. When it is 1, always returns 2.

PS: All models and foreign keys in mysql are configured correctly.

Comments Table:

id  |  name
10      Comment Test

Users Table:

id  |  name
20     User Test

Likes (Comment/User) Many to Many:

user_id  |  comment_id
20          10

Code:

$criteria = new CDbCriteria;
$criteria->select='*, COUNT(likes.id) AS count_likes'; // I believe the error is in the use of COUNT (likes.id).
$criteria->with=array('likes'=>array('on'=>'user_id=20'));
$model = Comments::model()->findByPk($_GET['id'], $criteria);

// Return Wrong Value
echo $model->count_likes; // Return 2 where should be 1. (I need to use this case)
echo count($model->likes); // Return right value 1.

Upvotes: 1

Views: 152

Answers (2)

soju
soju

Reputation: 25322

You should use Statistical Query, e.g. :

In your Comments model :

public function relations()
{
    return array(
        // ...

        // I assume your relation table's name is "likes"
        'likes'=>array(self::MANY_MANY, 'Users', 'likes(comment_id, user_id)'),
        'likesCount'=>array(self::STAT, 'Users', 'likes(comment_id, user_id)'),

        // ...
    );
}

Upvotes: 2

Willy Pt
Willy Pt

Reputation: 1855

Are you planning on selecting from the LIKES table and group by them by their userid and comment id?
If so, you can use GROUP BY Please take a look at the SQLFiddle here
http://sqlfiddle.com/#!2/bc29b8/1/0
SELECT uid, cid, COUNT(likes.cid) FROM likes GROUP BY uid, cid

Upvotes: 0

Related Questions