Laravel 4 model with attributes from multiple tables

0
votes

I would like to have a model that belongs to another one. In my controller, I'd like to get all items in that model, but I want the attributes from the table it belongs to as well. For example:

class Comment extends Eloquent {

    public function post()
    {
        return $this->belongsTo('Post');
    }

}

In my controller I can do the following to get the comment data:

$comments = Comment::first();

However, that will only give me data from the comments table (and no joined data from the posts table). I would like the data from the posts table for the row that each comment belongs to available as attributes to my Comment model. I know that I can also do the following to get the data from the posts table:

$comments = Comment::first();

The issue with doing it this way is that it uses two database queries (#1 to get the comment, and #2 to get the post data that it belongs to). Is there a way that I get the data from both tables into my model, equivalent to a join statement:

SELECT * FROM comments LEFT JOIN posts ON comments.post_id = posts.id

I Know that I build a join query manually without using my Comment or Post models, but I have several methods in my Comment model that I'd like to be able to use with the data that is retrieved. Anyone know how I can accomplish this?

3
sqljoinormlaravellaravel-4
You must first Retrieve the Post and then Retrieve your Comment based on your Post Like this $comments = $post->comments()->orderBy('created_at', 'DESC')->get(); – harishannam

3 Answers

4
votes

From the documentation on eager loading:

Thankfully, we can use eager loading to drastically reduce the number of queries. The relationships that should be eager loaded may be specified via the with method [...]

Using the with() parameter will use a constant number† of queries in a one-to-many relationship. Therefore, this is query should retrieve your comments with their related post in one query:

$comments = Comments::with('post')->get();

† A Constant number of queries as opposed to linearly increasing in query count per number of comments

1
votes

If you want to get the results only with one query done on the background, without using with(), you can use fluent and join().

SELECT * FROM comments LEFT JOIN posts ON comments.post_id = posts.id

is equal to:

DB::table('comments')
    ->join('posts','comments.post_id','=','posts.id','left')
    ->get();

But I think also adding a groupBy() statement will give you better results:

Example:

DB::table('comments')
    ->join('posts','comments.post_id','=','posts.id','left')
    ->groupBy('comments.id')
    ->get();

But I'd prefer to use the other answer in my projects:

Comment::with('post')->get();
0
votes

$comments:: all()->with ('post')->get()