How to join 4 tables to make 1 query

What is company related to?

You can eager load nested relationships like this:

$books = Book::with('author.contacts')->get();

Hello mstnorris,

Here are my relationships:

User Model

public function profile()
    {
        return $this->hasOne('UserProfile');
    }

public function policies()
    {
        return $this->hasMany('Policy');
    }

Policy Model

public function company()
    {
        return $this->hasOne('Company');
    }

You can do something like this I guess

$user = User::with('profile', 'policies.company')->where('id', '=', $userId)->get();

// Now you can access the company name like this
$user->policies->company->name

@karchoker please take a look at some Guidelines for posting on Laracasts.com it will help you with posting code and what to show us with regards to models and relations etc.

@mstnorris His question is quite clear... He already explained his models and the data he wants to receive from the database.

@mstnorris I already put the tags before my code.

@bobbybouwmann Thanks for your help, i tried what you tell me and i get this error:

$user_data=User::select('*')
            ->with('profile','policies', 'policies.company')
            ->where('users.id', '=', $userId)
            ->first();

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'companies.policy_id' in 'where clause' (SQL: select * from `companies` where `companies`.`policy_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

i think is something with my relationships

@bobbybouwmann I understand the question but wanted more specifics to give a better example.

Your with should look like this

->with('profile', 'policies.company')

@bobbybouwmann Thanks for your help. I did the change but still have the same error.

$user_data=User::select('*')
            ->with('profile','policies.company')
            ->where('users.id', '=', $userId)
            ->first();

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'companies.policy_id' in 'where clause' (SQL: select * from `companies` where `companies`.`policy_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10))    

But in the companies table i dont have the policy_id field, the relation key is policies.company_id = companies.id

Mmh I think you can't do it using Eloquent! You will need to do joins I guess

User::join('profile', 'profiles.user_id', '=', 'users.id')
    ->join('policies', 'policies.user_id', '=', 'users.id')
    ->join('companies', 'policies.company_id', '=', 'companies.id')
    ->where('users.id', '=', $userId)
    ->first();

Note: this is sample code, don't know for sure if this works but you get the idea ;)

@bobbybouwmann Thanks for your help. I did this code before but the problem is that i'm getting 10 records with almost the same fields(client.id, client.email, client.first_name.......) only field that change is policy_num. With eloquent i get an Object (the user) and inside this object i get an 2 arrays(user_profile, policies). Check this picture http://i.stack.imgur.com/reMtF.png I wanted to inside the policies array i can get other array with the company fields.

I get that ;) But as far as I know it's not possible using with()! Maybe someone else has ideas on this! @JarekTkaczyk

@bobbybouwmann Thank you very much for your help

Do you have a policy_id field in your companies table?

This might be a bit of a stretch, but have you tried using belongsTo instead of "hasOne" in your Policy model, and including a company_id in your policy database table?

Just a tip. It is not always the best thing to make 1 query. Sometimes 2 queries is 10 times better then 1 .

@shawnyv You definitely have relations wrong. The other side of hasOne/hasMany is always belongsTo, not hasOne.

I can't see what problem you had there apart from that.

Hi, like others said you should use a belongsTo relationship.

You can also automatically load the company relation whenever you load a policy (so that you don't end up with something like this Model::with('relationA', 'relationB', 'relationB.relationC'). This is done with the $with property in your model.

<?php
class Policy extends Model
{

    $with = ['company']; // Automatically load these relations

    public function company()
    {
        return $this->belongsTo('Company'); // same as $this->belongsTo('Company', 'company_id', 'id');
    }
}

And then in your controller you would do something like this.

$user = User::with('profile', 'policies')->find($userId);

Thank you very much to all for your answers, i will made the changes and post the results.

@SimonD Thanks for your answer, do you know why im getting this error when i put this code on the model

$with = ['company'];

syntax error, unexpected '$with' (T_VARIABLE), expecting function (T_FUNCTION)

@SimonD your code is missing the accessibility attribute ( Public, private, protected ). @karchoker add that and it should work

Thanks @em57

Thank you to all for your answers, is working good now. @SimonD @em57 @bobbybouwmann