Order By count(*) from other table in yii 2

Question

I have 2 table: videos and likes
video table has fields: id, title ..
likes table has fields: id, video_id, user_id

I would like to get the 10 most like videos. I can do it in sql by command:

SELECT videos.*, COUNT( likes.video_id ) AS countlike  
FROM videos LEFT JOIN likes ON videos.id = likes.video_id  
GROUP BY videos.id  
ORDER BY countlike DESC  
Limit 10

How can i implement it in ActiveRecord with yii 2

I would greatly appreciate with any help.
Thanks in advance!

Answer 1

First you should create ActiveRecord class for video and likes tables to make things easier.

The aim is to create the two following classes class Video extends \yii\db\ActiveRecord and class Likes extends \yii\db\ActiveRecord.

To do it easily, you should look at gii utility which will do it for you (available at yourdomain/gii/model). Fill the form for both your tables and you will be done.

Then you should be able to write your request:

$query = Video::find()
       ->select(['video.*', 'COUNT(likes.video_id) AS countlike'])
       ->join('LEFT JOIN', Likes::tableName(), 'videos.id=likes.video_id')
       ->groupBy('videos.id')
       ->orderBy(['countlike' => SORT_DESC])
       ->limit(10);
$data = $query->all();

In my opinion there is nothing magical in using ActiveRecord for complex requests. But it may avoid errors.

I highly recommend reading ActiveQuery documentation for more info about parameters of above methods. http://www.yiiframework.com/doc-2.0/yii-db-activequery.html

Hope it helps.

Answer 2

Try this:

SELECT DISTINCT (
videos.id
)id, videos . * , count( likes.id ) as count
FROM `videos`
LEFT JOIN likes ON videos.id = likes.video_id
ORDER BY count DESC