Model relations with multiple other models into one collection or list

I am looking for a good way to stay using Eloquent, but to gather data from various models (relations) and output into one collection/array or list. I would like to keep the different data into different models so I can easily reference them and as some of the data in the models can vary per row in the main model.

An example would be team rosters. Assuming I'd have a users table for auth to the site and personal info. I would have a team model. A particular user can be on many different teams. I would have a roster model. A team can have one roster. The roster can have many users. I would have a roles model. A user can have a different role per each team (player, manager, assistant, etc). The user can have a different jersey number per team and some other data like booleans for regestrations, etc.

I've tried pivot tables and I've tried eager loading the relations. I can't seem to find a good way to have many relations loaded from different models at the same time for one or many rows of that roster model.

In my mind I'm thinking the roster model would look something similar to below with foreign key IDs and possibly some columns containing data.

id  |  team_id  |  user_id  |  role_id   |   league_id  |  jerseyNum  |  registered
------------------------------------------------------------------------------------
 1  |     3    |     1      |      1     |       2      |     29      |     true
 2  |     2    |     2      |      1     |       1      |     9       |     false
 3  |     6    |     3      |      2     |       6      |     68      |     false
 4  |     9    |     1      |      2     |       3      |     19      |     true

In normal SQL terms it feels like a big join statement. I wanted to keep the models as I can reference relations in other parts of the app that don't involve all of this data. If I need to go away from Eloquent, that's fine I was hoping to figure it out as Eloquent makes things simpler.

Snapey

8 years ago

Level 122

Sounds like you are missing a member model

team has many members
member belongs to team
member belongs to user
member has a jersey number attribute
member belongs to a role

Is that easier to think about than roster?

$team = Team::with('members.user', 'members.role')->find(1);

you might need to change member to have many roles if team member needs to have more than one role. This would require a member_role pivot table

The main difference with my approach is I thing you were stuck with the idea that there was a single roster model which contained all the members, but this seemed to be the wrong concept?

jgrohol

8 years ago

Level 3

I think your way works, but not completely as well. I was just coding a bit and made a players model/table. Think of that as the same as your members model. When I get a team using a method, I used lazy eager loading to get the relations from the other tables like such:

public function getTeam(Team $team)
  {
      $team->load('player.user', 'player.role');
...

That works just fine. However, how can I use that same concept but also load or get direct values from that table that aren't related to another table? If you take jerseyNum for example, that's an int column that holds the Jersey Number for the player. If I try the following it's looking for a jerseyNum relation from the player model:

public function getTeam(Team $team)
  {
      $team->load('player.user', 'player.role', 'player.jerseyNum');
...

If I were to continue down this route (which seems to be a good path) the question is how can I do lazy eager loading with relations to other models yet grab direct data out of the player model directly all in the same method?

Snapey

8 years ago

Level 122

why is jersey not just a column on the player table?

1 like

jgrohol

8 years ago

Level 3

It is. It holds a value not a foreign key to another table though. So I can't eager load it as a relation. So when I do $team->load() with the nested eager loading, how can I get the value from the jerseyNum field instead of it looking for a relation?

Please or to participate in this conversation.