How to join 3 tables using laravel eloquent

Question

How can I apply this query in laravel 5? I have 3 table, Post, Profile, Comments

Post{id,title,body,user_id}
profile{user_id,last_name,first_name}
comments{comments,user_id,post_id}

I want to get this kind of structure:

post_id title post last_name first_name

and below are the comments

comments last_name fist_name

Answer 1

//post model

public function comments(){
    return $this->hasMany('App\Comments', 'post_id', 'id');
}

//comments model

public function user(){
    return $this->belongsTo('App\Profile', 'user_id');
}

//profile model

public function comments(){
    return $this->hasMany('App\Comments');
}

and i call it in the controller using:

$posts = Posts::with('userProfile','comments', 'comments.user')->get();

Then it works:) Thank you for some help and idea :)

Answer 2

If you need JUST those columns, you have to do something like this:

$users = DB::table('comments')
        ->join('profile', 'comments.user_id', '=', 'profile.user_id')
        ->select('comments.comments', 'profile.last_name', 'profile.first_name')
        ->get();

Anyway, I'd recommend using relations in your models if you are using the data in different places (or different structures).

P.S:

post_id  post  last_name  first_name

There's no "post" field in any table, do you need the post title?