Say you have Users
and Roles
in a many-to-many relationship. Standard setup with following tables:
user role user_role
user_id role_id user_id
email name role_id
Preferably with as few queries as possible, what's a good way to load all users with all tags belonging to them? That is, I'd like to end up with users, each having an array of roles.
I'm currently thinking of using the following, which'll requires two queries.
- Get all users with group concatenated role ids.
- Get all role names in separate query
- Loop through users, explode role ids and connect them with names from role query as we go.
Any good/better/more efficient/cleaner/quicker alternatives? Any good way to do this in a single query?