Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

judodan's avatar

Convoluted DB/SubQuery issue

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.

0 likes
2 replies
judodan's avatar

I did figure out how to replicate, but it's a lot of code for a query that's mostly the same. Will check out your link, thanks.

Please or to participate in this conversation.