Display news based on number of comments and current date in Yii2?

Question

MySQL

Hi all.
Faced such a task, there is a table with News and Comments, how to sort the output of News Desc depending on the date of publication, there are no problems

'query' => News::find()->where(['visibility'=>1])->orderBy('date DESC');

I don't understand how to use Yii2 or a pure SQL query to select news and sort them DESC depending on the number of Comments and the date of publication DESC.
Forgot to mention, the Comment table is located in Another Database
Any help would be greatly appreciated.
Thanks in advance.

Answer 1

Not?

SELECT *
FROM news n
  INNER JOIN news_comments nc ON n.news_id = nc.news_id
GROUP BY n.news_id
ORDER BY COUNT(nc.comment_id) DESC, n.date DESC

You can do the same with Query

News::find()->where(['news.visibility'=>1])
->innerJoin(['nc' => NewsComments::tableName()], 'nc.news_id = news.id')
->groupBy('news.id')
->orderBy('COUNT(nc.id) DESC, news.date DESC');