get user with most articles and its amount with active record or query

Question

I want to get the user that wrote the most articles. I do so fine in two ways with ActiveRecord like the following:

$table = Articles::find()
        ->select('articles.*, COUNT(*) AS cnt')
        ->with('user','userDetails')
        ->groupBy('articles.user_id')
        ->orderBy(('cnt DESC'))
        ->limit(10)
        ->offset($offset)
        ->all();

and with a query like the following:

$query = (new Query())
        ->select('articles.user_id, COUNT(*) AS num_articles')
        ->from('articles')
        ->join('LEFT JOIN', 'user_details', 'user_details.user_id = articles.user_id')
        ->groupBy('articles.user_id')
        ->orderBy('num_articles DESC')
        ->limit(10)
        ->offset($offset)
        ->all();

The problem is that the ActiveRecord gives me further needed informations userDetails that I need. But I do not get the amount of articles of user that should be on cnt
With the Query I get the user_id and the amount of articles. But I do not get it working by joining with userDetails. All of these does not work: LEFT JOIN, RIGHT JOIN, INNER JOIN.

I am interested in resolving both for learning, but for concrete I need help with the ActiveRecord problem.

score 1 · Accepted Answer · answered Apr 17 '15 at 14:41

Okay well I solved it for the ActiveRecord. The ActiveRecords needs a public $var; in the Model. So to get the amount you have to add the mentioned public... to your Model so that in my case:

public $cnt; extends the ActiveRecord of Articles

now I can access it with the given Request in my Question. But this just solves the first point. I am still interested in the second way for more complex Queries.

score 1 · Answer 2 · answered Apr 17 '15 at 14:57

1

I dont have much idea about active record but I think the below is something what you are looking for

select * from user_details where user_id in 
      (select A.user from 
         (select user_id as user, COUNT(*) AS num_articles 
           from articles group by user_id order by num_articles desc LIMIT 10)A
      );

answered Apr 17 '15 at 14:57

Lalit Agarwal

2,354
1
14
18

This query however will not give you the count of records. In case you want both user details and count of records then you should look at the rank method. http://stackoverflow.com/questions/3333665/rank-function-in-mysql – Lalit Agarwal Apr 17 '15 at 15:14

score 1 · Answer 3 · answered Apr 18 '15 at 10:32

for second point you should include required column from joined table to select statement:

$query = (new Query())
        ->select('articles.user_id, COUNT(*) AS num_articles, user_details.username as username')
        ->from('articles')
        ->join('LEFT JOIN', 'user_details', 'user_details.user_id = articles.user_id')
        ->groupBy('articles.user_id')
        ->orderBy('num_articles DESC')
        ->limit(10)
        ->offset($offset)
        ->all();

get user with most articles and its amount with active record or query

3 Answers3