Does Eloquent prepare the output of the data in the collection when relationships are eager loaded?

Published 1 year ago by Ruffles

Hello there!

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?

Best Answer (As Selected By Ruffles)
Ruffles

I went with a different route, using group_concat and group_by, a little bit different than @jimmck 's example. Now I am getting them as strings which can be easily used in the future.

Thank you all for your contribution in this discussion!

jlrdw
jlrdw
1 year ago (215,260 XP)

@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.

jimmck
jimmck
1 year ago (61,815 XP)

@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.

Snapey
Snapey
1 year ago (864,725 XP)

One of the differences with Eloquent ORM is that the relations are nested and not duplicated on every row - as you would get with a join.

Maybe a GroupBy on the email is needed on your query builder approach?

jlrdw
jlrdw
1 year ago (215,260 XP)

The

User::with('roles')->get()

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.

Ruffles
Ruffles
1 year ago (289,060 XP)

My table schema is simple:

users table

id
email
password
...

roles table

id
name

role_user table

role_id
user_id

I am using the same schema from the package spatie/laravel-permission

jimmck
jimmck
1 year ago (61,815 XP)

@Ruffles So ideally you would like a result set row of all categories for a user and their email address?

Ruffles
Ruffles
1 year ago (289,060 XP)

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

]
jimmck
jimmck
1 year ago (61,815 XP)

@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

jimmck
jimmck
1 year ago (61,815 XP)

http://onlybluefeet.com/2015/01/18/how-to-rotate-rows-into-columns-in-mysql/

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

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
jlrdw
jlrdw
1 year ago (215,260 XP)

You are looking for a report style nested foreach, see https://laracasts.com/discuss/channels/laravel/complex-query-for-a-report

Ruffles
Ruffles
1 year ago (289,060 XP)

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.

jlrdw
jlrdw
1 year ago (215,260 XP)

@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.

Ruffles
Ruffles
1 year ago (289,060 XP)

I went with a different route, using group_concat and group_by, a little bit different than @jimmck 's example. Now I am getting them as strings which can be easily used in the future.

Thank you all for your contribution in this discussion!

Please sign in or create an account to participate in this conversation.