Laravel OrderBy OneToMany Relation

Question

У меня есть 2 таблицы events_users: в ​​которых хранится информация о людях, которые посетили определенные события. Многие люди посещают много событий, следовательно, используя эту таблицу ссылок.

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

И events_user_score: который записывает разные оценки людей на мероприятиях. На разных мероприятиях может быть много разных типов очков, поэтому это в отдельной таблице, а не в столбцах первой таблицы.

+--------+---------+------------+--------+
|  eid   | user_id | score_type | number |
+--------+---------+------------+--------+
| event1 | user1   |          1 |      4 |
| event1 | user1   |          2 |      3 |
| event2 | user3   |          1 |      6 |
| event1 | user1   |          2 |      5 | <- this row could not exist as
+--------+---------+------------+--------+    no duplicates possible with same eid, user_id & score_type

Я хотел бы иметь возможность отсортировать выборку из первой таблицы на основе рисунков во второй таблице. Так что хотелось бы получить совместную работу, которая даст такой результат. По сути, превращение Score_type в столбцы с числом в качестве значения.

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

До сих пор я пытался использовать hasMany от laravel, но я не могу сортировать, используя эту технику. Итак, я пытаюсь попробовать левое соединение какого-то типа, но запуталось, потому что теперь я не получаю никаких очков назад:

$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();

Предположим, что $lastWeeksEvents возвращает список событий с EID, совпадающими с событиями в первой таблице.

Отредактируйте - хотя это и есть Score_types 1 - 6. Давайте попробуем получить типы 1 и 2 в виде столбцов. Если это облегчает?

Answer 1

Вы также можете использовать подзапрос следующим образом:

Простой SQL:

select u.eid, u.user_id, u.slot_no, 
(select number from events_user_score s where s.eid = u.eid and s.user_id = u.user_id and s.score_type = 1) as `1`,
(select number from events_user_score s where s.eid = u.eid and s.user_id = u.user_id and s.score_type = 2) as `2`

from events_users u

Теперь, используя это в Laravel:

//Get all score types
$score_types = DB::table('events_user_score')
->select('score_type')
->groupBy('score_type')
->get();

$event_query = Events_user::from(DB::raw('events_users u'))
->select('eid', 'user_id', 'slot_no');

//Add score types as sub queries
foreach ($score_types as $score) {
    $event_query->addSelect(DB::raw('(select number from events_user_score s where s.eid = u.eid and s.user_id = u.user_id and s.score_type = ' . $score->score_type . ') as `' . $score->score_type . '`'));
}

//Example, to sort by score type 1
$data = $event_query->orderBy('1')->get();

Не забудьте добавить use DB; в верхней части вашего кода.

Answer 2

Чтобы получить желаемые результаты, вы можете написать следующее в простом SQL как

select e1.*,
max(case when e2.score_type  =1 then e2.number else 0 end ) score_type1,
max(case when e2.score_type  =2 then e2.number else 0 end ) score_type2
from events_users e1
left join events_user_score e2 on e1.eid = e2.eid
                                  and e1.user_id = e2.user_id
group by e1.eid, e1.user_id,e1.slot_no

демонстрация

Приведенный выше запрос предполагает, что в качестве 1 и 2 могут быть два параметра Score_types, если их больше, приведенный выше запрос не будет выполним для такого типа набора результатов.

То же самое, используя конструктор запросов laravel, можно записать как.

$attendees = Events_user::from('events_users as e1')
                          ->select(DB::raw("e1.*,max(case when e2.score_type  =1 then e2.number else 0 end ) score_type1, max(case when e2.score_type  =2 then e2.number else 0 end ) score_type2"))
                          ->leftJoin('events_user_score as e2', function ($join) {
                                $join->on('e1.user_id', '=', 'e2.user_id');
                          })
                          ->groupBy('e1.eid')
                          ->groupBy('e1.user_id')
                          ->groupBy('e1.slot_no')
                          ->get();

Вышеупомянутое решение присоединиться events_users стол со вторым столом events_user_score сопоставляя событие и пользователя. Агрегация вызывается для получения уникальных данных для eid, user_id & slot_no