1

Let's say I have a table users with thousands of records, each with their name.

My goal is to draw a pie chart showing the most common names. I want to take, for example, the 9 most common names, and put the rest into a 'others' group.

I can't figure out how to use groupBy() and take(), or whatever, to achieve this.

Any ideas?

Oscar
  • 61
  • 1
  • 8
  • Do you want to show the name along with their count on pie chart for the name which are repeated more than 9 time and remaining names as others – sumit Dec 14 '17 at 21:34

1 Answers1

0

You can try using groupBy and count raw

$groups = DB::table('users')
            ->select(DB::raw('count(*) as user_count, name'))
            ->groupBy('name')
            ->orderBy('user_count', 'desc') //lorder desc or asc
            ->limit(5) //number of groups
            ->get();

        dd($groups); you will see the user_count and name

Data for your pie chart would be user_count/total_user

nivanmorgan
  • 149
  • 1
  • 16
  • 1
    Yes, that works fine! I can get any number of groups with most common names. Now I need to put the rest of the users in the "others" group, but since I have a subgroup, I can (easily) get the complementary group. Thanks! – Oscar Dec 14 '17 at 22:15
  • Yes, the question was very general so I couldn't create foreach statements and show you the rest of the way to code it. But I hope this helps a little. – nivanmorgan Dec 14 '17 at 22:35
  • This answer might be of some help too. https://stackoverflow.com/questions/18533080/laravel-eloquent-groupby-and-also-return-count-of-each-group?rq=1 – nivanmorgan Dec 15 '17 at 14:13