Laravel OrderBy OneToMany Relation

I have 2 tables events_users: Which stores information about people that have attended certain events. Many people attend many events, hence using this linking table.

+--------+---------+---------+
|  eid   | user_id | slot_no |
+--------+---------+---------+
| event1 | user1   |       0 |
| event1 | user2   |       1 |
| event2 | user3   |       0 |
+--------+---------+---------+

And events_user_score: Which records the different scores of people at the events. At different events there can be lots of different score types, which is why this is in its own table, rather than as columns in the first table

+--------+---------+------------+--------+
|  eid   | user_id | score_type | number |
+--------+---------+------------+--------+
| event1 | user1   |          1 |      4 |
| event1 | user1   |          2 |      3 |
| event2 | user3   |          1 |      6 |
+--------+---------+------------+--------+

I'd like to be able to sort a selection of the first table, based on the figures in the second table. So would love to get a join working that would get a result like this. In effect turning score_type into columns, with the number as the value.

+--------+---------+---------+---+---+
|  eid   | user_id | slot_no | 1 | 2 |
+--------+---------+---------+---+---+
| event1 |   user1 |       0 | 4 | 3 |
| event1 |   user2 |       1 |   |   |
| event2 |   user3 |       0 | 6 |   |
+--------+---------+---------+---+---+

So far i've tried using laravel's "hasMany" but i can't sort using that technique. So am looking at trying a left join of some type, but have got really confused as now i'm not getting any score back:

$attendees = Events_user::whereIn("events_users.eid", $lastWeeksEvents->pluck('eid'))->where("slot_no",">=",0)
                            ->leftJoin('event_user_score', function ($join) {
                                $join->on('events_users.eid', '=', 'event_user_score.eid')
                                     ->where('events_users.uid', '=', 'event_user_score.uid');
                            })
                        ->get();

Assume that $lastWeeksEvents returns a list of events with the eid's matching those in the first table.

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2iLSprR
via IFTTT