Laravel 4 model with attributes from multiple tables

Question

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?

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
– harishannam, Commented Sep 11, 2013 at 4:45

fideloper · Accepted Answer · 2013-09-11 20:07:06Z

4

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

edited Sep 11, 2013 at 20:07

answered Sep 11, 2013 at 12:54

fideloper

12.3k1 gold badge43 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

matt Over a year ago

If I add an event listener for the queries, it does show that there are two separate queries still running to get the data: select * from comments select * from posts where posts.id in (?, ?) However, if I am retrieving MANY comments, it is still only using 2 total queries instead of 2 queries for EVERY comment. I think that should be good enough for me.

fideloper Over a year ago

Good to know! It appears it's not "one" but rather "few" (constant) queries. Thanks.

Arda · Accepted Answer · 2013-09-11 08:08:09Z

1

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();

answered Sep 11, 2013 at 8:08

Arda

6,9633 gold badges53 silver badges70 bronze badges

Comments

kaning · Accepted Answer · 2013-09-11 06:50:51Z

0

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

answered Sep 11, 2013 at 6:50

kaning

1472 silver badges8 bronze badges

4 Comments

Arda Over a year ago

::all() is not required if you're getting the collection like that. $comments::with('post')->get() is the correct usage

matt Over a year ago

Unless I'm mistaken, this method also uses two database queries to retrieve the information (#1 for comment data and #2 for post data). Isn't there a way to retrieve it all using a single query?

Arda Over a year ago

@matt You will need to use fluent and join() then. But @kaning 's approach is more laravelish.

fideloper Over a year ago

Using with() DOES use one query. Not using with() and calling $comment->post later in execution triggers a 2nd query. See the docs on eager loading for more information.

Collectives™ on Stack Overflow

Laravel 4 model with attributes from multiple tables

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related