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
How can i implement it in ActiveRecord with yii 2
I would greatly appreciate with any help.
Thanks in advance!
Best How To :
First you should create ActiveRecord class for
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')
->orderBy(['countlike' => SORT_DESC])
$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.