Published 1 year ago by Ruffles
I am working on this project where I use the Fluent Query Builder and I am trying to recreate the same output from the eloquent relationship eager loading,
User::with('roles')->get() for example, with join statements.
As a result I had duplicate rows for obvious reasons. I had a list of repeating email addresses with multiple role names for a many to many relationship.
My question is: Do I have to manually structure the objects where I have a nested object(s) for the relationships?
@Ruffles the ORM has all query builder methods. But handling relations (the way it does) and querybuilder joins are two different worlds. Querybuilder alone is more like writing regular queries (in a way). Orm and querybuilder overlap on some things.
Pick one or the other.
Edit, if you meant proper binding, yes.
@Ruffles Without seeing your schema. It seems you want a distinct email address for 1 or more roles? You can make the driving query a sub-select on the table containing the email addresses. As long as the email table has the needed join fields. Your From may have the sub-select aliased as possible-emails, select them as distinct. You have to play around with the SQL. Need table schema.
generates two queries, no different than something like
select * from user where userid = 1;
and second query
select * from roles where userid = 1;
of course field names are example only.
In fact, I forget the video, but Jeffrey explains how a one to many reduces down to two queries.
One thing folks forget, orm is a shortcut language, all reduces down to normal sql at runtime. Sometimes the shortcuts are actually harder to figure out than regular sql.
Edit: Almost forgot, @jimmck answer two thumbs up.
The desired output would be:
User => [ 'email' => '[email protected]', // some other fields go here 'roles' => [ Role => [ 'name' => 'super_admin' ], Role => [ 'name' => 'client_manager' ] ], // other users
this is how eloquent outputs the data in the collection but for some reason I get the following when I use the fluent query builder with joins:
[ stdClass => [ 'email' => '[email protected]', // some other fields go here 'role_name' => 'super_admin' ], stdClass => [ 'email' => '[email protected]', // some other fields go here 'role_name' => 'client_manager' ], // other users ]
@Ruffles I think I see. To use joins and SQL you can use GROUP_CONCAT to flip rows into columns (sort of).
This query will return 2 rows the user_name and comma delimited list of roles.
You could then split the roles out.
Regardless you have to manipulate the result set to get the desired shape.
But this gets all roles for a user (or sub in email)
select r1.name, group_concat(r1.role) Roles from (select roles.name role, u1.name name from myroles roles, myuser_roles mu, myusers u1 where roles.id = mu.role_id and u1.id = mu.user_id) r1 group by r1.name
Name Roles Denise leader,admin,sales Jim admin,leader,support Lissa sales
Schema's from example
CREATE TABLE IF NOT EXISTS myuser_roles ( user_id int, role_id int ) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE IF NOT EXISTS myusers ( id int NOT NULL, name varchar(50) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE IF NOT EXISTS myroles ( id int NOT NULL, name varchar(50) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
I have managed to figure out a way to group the output in a way Eloquent does it, using arrays and standard classes like in my example. The only problem I have now is the pagination is not consistent. Different number of results are displayed depending on the roles assigned to the users.
For example: If all users have all 4 roles (will probably never happen, they might have 3 of those 4 roles in some cases.) and I have 30 records per page set, I will get only 9 records back. If I increase the number of records per page to 200, I will have 52 users returned. This is happening because the pagination is ran against the role_user table and with the output grouping (or transformation), I cut down the array of items down.
@Ruffles use double pagination. Don't know your exact terms but as an example have the user as a header now under that header paginate the roles. Have a link next to that header that says next user and likewise when you go to next user now paginate that users roles. A little work figuring out all of this but I have done it successfully and it works.
As I don't really use the orm that much I did it using the query builder using two queries. In appends you have to pass the information for each thing if you are on like which user, and role. I wrote a small custom paginator for the one item for header, calculating each time.
It's been a little while since I had to do that I will try to dig out some example code, on tablet now. But I think you get the idea of a simple double paginator just takes a little bit of calculating and little bit of work in the controller.
Edit, of course with a smaller amount of data like that it probably doesn't matter which technique is used but the double pagination works great when you're talking large result sets.