Jul 10, 2017

Level 1

ORM select model with related data and view

Hi I've been battling the next orm fight. I want to show data from 3 related tables. user, roles, companies and role_user pivot table.

User model

public function roles() {
    return $this
            ->belongsToMany('App\Role')
            ->withTimestamps();
}

public function Companies() {
    return $this->belongsTo(Companies::class, 'id');
}

Role model

public function users() {
    return $this
            ->belongsToMany('App\User')
            ->withTimestamps();
}

Companies model

public function User() {
        return $this->hasMany(User::class, 'companies_id');
}

UsersController

public function show(Request $request, $id) {

// $user = User::findOrFail($id); <- gets data from user model without companies and roles

// nth attempt..
$user = User::with('roles','companies')->findOrFail($id);

// How do I retrieve/add/join data from companies and roles for specific user?

return view('users.view', compact(['user']));

view

Username: {{ $user->username }}

Email: {{ $user->email }}

Company: {{ $?????->????->company_name  }}

Roles: @foreach ($????? as $role)
{{ $role->name  . ',' }}
@endforeach

How to correctly add/append the related data and print it out on view? Thank you

tykus

8 years ago

Best Answer

Level 104

Your companies relation on the User model is using the id field as the foreign key; is that correct? Your inverse relationship on the Companies (would make more sense if it was singular) model uses a companies_id foreign key.

The ORM will take care of joining the relations to the correct User instance if you have defined the relations correctly, so :

Username: {{ $user->username }}

Email: {{ $user->email }}

Company: {{ $user->companies->company_name  }}

Roles: @foreach ($user->roles as $role)
{{ $role->name  . ',' }}
@endforeach

1 like

mikevrind

8 years ago

Level 7

Company: {{ $user->companies->company_name  }}

If a user belongs to a single company, rename the relation to 'company' to add more clarity to your code. Within a view you can use the name of the relation(s) to get the columns of the related table.

Ah, @tykus beat me to it :p

Snapey

8 years ago

Level 122

and make sure you are careful about capitals. Your relation Companies will never be loaded if you load it as companies

2 likes

Snapey

8 years ago

Level 122

conventions to make life easier

models are always singular and start with a capital, e.g. Company
tables are always plural and start with lowercase e.g. companies
relations should be plural for hasMany or singular for belongsTo, E.g. company()
foreign key columns are model name + _id all in lowercase
primary key is usually just id

All the above can overridden but it's just more work if you need to

3 likes

baguus

8 years ago

Level 1

Hi all Thank you for your answers. I got roles working. Companies are working, but I'm getting the wrong company for specific user. I guess I need to correct the relations and singular/plural naming.

@Snapey Does Laravel know how to correctly turn singular to plural e.g. companY -> companIES and vice versa?

tykus

8 years ago

Level 104

I expect your companies relationship is still not fixed:

// app/User.php

public function Companies() {
    return $this->belongsTo(Companies::class, 'id'); // id here should be companies_id, i.e. the foreign key on the users table 
}

This fix is based on the original posted relation. If you stick with Laravel's conventions, then you do not have to explicitly define the foreign key on the relationship - as @Snapey posted above, you would have a companies table and a Company model; further you would have a company() relationship on the User model:

public function company()
{
    return $this->belongsTo(Company::class);
}

1 like

baguus

8 years ago

Level 1

Aaaaaaa

// id here should be companies_id, i.e. the foreign key on the users table

I was looking this the other way around :) Thank you. I think I'll change it to singular as per convetion.

Please or to participate in this conversation.