I hope there's some real DB experts who can chime in... I've inherited a scheduling system (10 years old) and I'm trying to move the hand-coded database to Eloquent. However, I'm stumped with the following query with multiple sub-queries and I'm wondering what's the best (easiest?) way to do this with Eloquent:
SELECT u.last_name, u.first_name, u.email, ua.description AS position FROM (
SELECT uu.user, min(uu.role) AS role FROM (
SELECT uu.user, max(ua.rights) AS max_rights FROM event k
JOIN license b ON k.event_id=<event id> and k.license=b.license_id
JOIN license_role bu ON b.license_id=bu.license and bu.creation=1
JOIN role ua ON bu.role=ua.role_id
JOIN user_role uu ON bu.role=uu.role_id
GROUP BY uu.users
) y
JOIN user_role uu ON y.user = uu.user
JOIN role ua ON y.max_rights = ua.rights AND ua.role_id = uu.role
GROUP BY uu.user
) x
JOIN user u ON x.user=u.user_id
JOIN role ua ON x.role=ua.role_id
JOIN user_role uu ON uu.role = ua.role_id AND uu.user = u.user_id
ORDER BY ua.rights DESC, ua.role_id
Quick details for those who have gotten this far.... This is an event scheduling system where a user needs a specific license to attend the event. (admin staff are also 'users' who have extra rights to admin stuff)
user has 1 license and multiple roles (defined in user_roles),
event has license id (defining who can attend),
role has rights (to do other stuff) as well as rights to administer events based on the license level (license_role)
What I'm trying to do: When given a specific event id, I want to get a list of those who have the rights to create/administer events at the specified event's license level.
It may be that some of this can be removed, given some of the other queries I've re-engineered so far, I'm not confident that the original author's query is the simplest solution.
Thanks for any ideas/help.