CODEWITHSUNDEEP
phpmysqllaravel-5eloquent
Luna
Luna

Reputation: 557

How to sort values in same mysql column based on count or number of occurences

I am trying to sort a laravel collection by count. I have a DB: USERS , in my DB i have a column named State which stores the user's home state example: Florida

I'm pulling the collection something like this:

public function friends()
{
    return $this->friendsOfMine()
        ->wherePivot('accepted', true)
        ->get()
        ->merge(
            $this->friendsOf()
                ->wherePivot('accepted', true)
                ->get()
        );
}

then in the view:

@foreach ($user->friends() as $friend)
{{$friend->state}}
@endforeach

I want to sort the states in order from greatest amount of friends from state to least amount of friends from state and show the count (total amount of friends for that state)

example: If Newyork has 100 friends, Texas has 70 friends, and Florida has 20 friends, then the result would be:

Newyork 100

Texas 70

Florida 20

I know i can use functions like count() , sort(), and sortBy(), but I don't know how to use it for this case, being that I have the values in the same column and want to sort them by count() , based on the number of occurrences the states appear.

Upvotes: 0

Views: 268

Answers (4)

Honk der Hase
Honk der Hase

Reputation: 2488

You may come in trouble with selects in select, since inner queries are fired again for each outer record. Better normalize your tables and use JOINs

Consider the following test database (incl. data)

CREATE TABLE `state` (
  `state_id` int(11) NOT NULL,
  `name` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `state` (`state_id`, `name`) VALUES
(1, 'Florida'),
(2, 'California'),
(3, 'Wisconsin'),
(4, 'New Jersey');

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL,
  `state_id` int(11) NOT NULL,
  `name` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `user` (`user_id`, `state_id`, `name`) VALUES
(1, 1, 'Person 1'),
(2, 2, 'Person 2'),
(3, 1, 'Person 3'),
(4, 3, 'Person 4'),
(5, 1, 'Person 5'),
(6, 1, 'Person 6'),
(7, 1, 'Person 7'),
(8, 3, 'Person 8');

CREATE TABLE `user_friend` (
  `user_id` int(11) NOT NULL,
  `friend_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `user_friend` (`user_id`, `friend_id`) VALUES
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(1, 7),
(1, 8),
(2, 1),
(2, 5),
(2, 7),
(2, 8);

ALTER TABLE `state`
  ADD PRIMARY KEY (`state_id`);

ALTER TABLE `user`
  ADD PRIMARY KEY (`user_id`);

ALTER TABLE `user_friend`
  ADD PRIMARY KEY (`user_id`,`friend_id`);

The "friend_id" of user_friend is connected back to the user table (to another user).

I'm using 8 persons from 4 different states, only 2 have friends. Most people live in Florida.

Now query a list of people who have friends, together with name and number of friends in the state, sorted descending.

SELECT 
  u.name as username,
  s2.name as friend_state,
  count(s2.state_id) as friends
FROM 
  `user` u
INNER JOIN 
  user_friend uf on u.user_id = uf.user_id
INNER JOIN 
  user f on f.user_id = uf.friend_id 
INNER JOIN 
  state s2 on s2.state_id = f.state_id
GROUP BY uf.user_id, s2.state_id
ORDER BY count(s2.state_id) DESC


+----------+--------------+---------+
| username | friend_state | friends |
+----------+--------------+---------+
| Person 1 | Florida      | 4       |
| Person 1 | Wisconsin    | 2       |
| Person 1 | California   | 1       |
| Person 2 | Florida      | 3       |
| Person 2 | Wisconsin    | 1       |
+----------+--------------+---------+

Thanks for reading ;)

Upvotes: 0

user1817927
user1817927

Reputation:

If you want "IN SAME mysql column" order the data, you need to make some kind of RAW query for create a TEMPORAL COLUMN IN QUERY and with it make counter and sort data (colTempName)

you need to do someting like:

SELECT col1,col2,state,count(*) AS colTempName
FROM [...]
( WHERE [...] / JOIN [...] )
GROUP BY columnbased_name_todo_count
ORDER BY colTempName,state

Upvotes: 0

ldg
ldg

Reputation: 9402

Your can do it in one sql query like this:

SELECT state, friends, (SELECT SUM(friends) FROM friend_tbl) AS friend_total
FROM friend_tbl ORDER BY friends DESC

It's going to look a little odd since "friend_total" will be repeated, but this is one approach.

STATE    |FRIENDS|FRIEND_TOTAL
new york |100    |190
texas    |70     |190
florida  |20     |190

Upvotes: 0

alepeino
alepeino

Reputation: 9771

I want to sort the states in order from greatest amount of friends from state to least amount of friends from state and show the count (total amount of friends for that state)

// Get a collection of state names as keys
// and the frequency of each state as values,
// sorted by the frequency descending

$states_count = $user->friends->groupBy('state')->map(function ($states) {
    return $states->count();
})->sort()->reverse();

If you then want the friends list itself to be sorted by that state frequency order,

// (If the states of any friends pair have equal frequency
// in the states list, sort those by state name alphabetically).

$friends_sorted = $user->friends->sort(function ($f1, $f2) use ($states_count) {
    if ($states_count->get($f1->state) < $states_count->get($f2->state)
      return 1;
    if ($states_count->get($f1->state) > $states_count->get($f2->state)
      return -1;

    return strcmp($f1->state, $f2->state);
});

Upvotes: 2

Related Questions