Counting order (*) from another table in yii 2
I have 2 tables: video and likes
The video table has fields: id, title ..
likes the table has fields: id, video_id, user_id
I would like to get the 10 most similar 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 using yii 2
I would really appreciate any help. Thanks in advance!
source to share
You must first create an ActiveRecord class for tables video
and likes
to keep things simple.
The goal is to create the next two classes class Video extends \yii\db\ActiveRecord
and class Likes extends \yii\db\ActiveRecord
.
To do this easily, you should look at the gii utility that does it for you (available on your / gii / model domain). Fill out the form for both tables and you will be done.
Then you should be able to write your query:
$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 about using ActiveRecord for complex queries. But he can avoid mistakes.
I highly recommend reading the ActiveQuery documentation for more information on the parameters of the above methods. http://www.yiiframework.com/doc-2.0/yii-db-activequery.html
Hope it helps.
source to share