CODEWITHSUNDEEP
phppostgresqlyii2yii2-advanced-app
clemente aranguiz
clemente aranguiz

Reputation: 39

Order a table from a related table in Yii2

I have a table named "video" that does not have "likes" in it, because i have created another table "video-like" to join each like to the "user". But i want to sort the videos by likes, and i dont know how to do that.

I am using Yii2 framework and PostgreSQL.

In this example i created a query that is sorted from new to old videos. Thats easy because i dont have to access another table to do it.

$this->orderBy(['created_at' => SORT_DESC]);

Any help is usefull !

Upvotes: 0

Views: 112

Answers (1)

mehri abbasi
mehri abbasi

Reputation: 155

You can have 2 nested select.

$query = Model()::find();
$subQuery = ModelLikes()::find()
            ->select("COUNT(*) AS amount")
            ->groupBy([
                ModelLikes::tableName() . '.source_id'
            ])
            ->orderBy(['amount' => SORT_DESC]);
$query->leftJoin(['T' => $subQuery],
            Model::tableName() . ".id= T.source_id");
$data = $query->all();

you should change model's name and field based on your models.

source_id is video's id in likes table

Upvotes: 1

Related Questions