count and order relationship values

I have posts with authors. Authors could be admin, editor, customer etc... I want to count posts by each type and find who which type posted the most and which one the least.

e.g. output I want is:


admin (43 posts)
customer (21 posts) //I don't actually need this one
subscriber (3 posts)

So I can end up with variables like


$mostPosts; // should be equal to 'admin' 
$leastPosts; // should be equal to 'subscriber'


//ideally I would have something like:

$array = [
    'mostPosts': {
        'type': 'admin',
        'count': 43
    },
    'leastPosts': {
        'type': 'subscriber',
        'count': 3
}
    ];


//starting from...

$posts->with('author')->....

With this, speed of execution is most important

Nakov

6 years ago

Level 73

Can you please share how do you store the role of the author? The raw query can be something like this:

select authors.type, count(posts.id) from authors
join posts on posts.author_id = authors.id
group by authors.type

You can try to execute this in your DB and make sure that it gives the correct results, then we can make an eloquent version out of it.

Please or to participate in this conversation.