eloquent-2

Previously we dealt with simple relations defined in Eloquent models and eager loading them in order to save database queries.

Now imagine nested relations like this:

1
2
3
4
5
6
7
 
CATEGORY has many POSTS
POST belongs to one CATEGORY
 
POST has many COMMENTS
COMMENT belongs to one POST
 

So there are one-to-many relations between posts & categories as well as comments & posts.

Above relations look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
// app/models/Category.php
class Category extends Eloquent {
    public function posts()
    {
        return $this->hasMany('Post');
    }
}
 
// app/models/Post.php
class Post extends Eloquent {
    public function category()
    {
        return $this->belongsTo('Category');
    }
 
    public function comments()
    {
        return $this->hasMany('Comment');
    }
 
}
 
// app/models/Comment.php
class Comment extends Eloquent {
    public function post()
    {
        return $this->belongsTo('Post');
    }
}
 

 

Now, imagine you would like to list all comments for given category.

Naturally we won’t loop through all posts related to the category, as it would be inefficient (n+1 problem), so consider 2 possible solutions:

  1. Load far relation (comments) based on close relation (posts) = nested relationship
  2. Load far relation only (comments) without close (posts)

What you want to do depends on whether you want or not loading posts too.

Here’s how to achieve no. 1 …

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
[1] > Category::with('posts.comments')->find(1)->toArray();
array(
  'id' => '1',
  'name' => 'noname',
  'posts' => array(
    0 => array(
      'id' => '1',
      'title' => 'first',
      'category_id' => '1',
      'comments' => array(
        0 => array(
          'id' => '1',
          'body' => 'This was awesome!',
          'active' => '1',
          'post_id' => '1'
        ),
        1 => array(
          'id' => '2',
          'body' => 'Active comment 1',
          'active' => '1',
          'post_id' => '1'
        ),
        2 => array(
          'id' => '3',
          'body' => 'Inactive comment 1',
          'active' => '0',
          'post_id' => '1'
        ),
        3 => array(
          'id' => '4',
          'body' => 'Active comment 2',
          'active' => '1',
          'post_id' => '1'
        ),
        4 => array(
          'id' => '6',
          'body' => 'Inactive comment 2',
          'active' => '0',
          'post_id' => '1'
        )
      )
    ),
    1 => array(
      'id' => '3',
      'title' => 'some long title',
      'category_id' => '1',
      'comments' => array(
 
      )
    )
  )
)
 

… and what Eloquent does behind the scenes:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
[2] > DB::getQueryLog();
array(
  0 => array(
    'query' => 'select * from `categories` where `id` = ? limit 1',
    'bindings' => array(
      0 => 1
    ),
    'time' => 1.68
  ),
  1 => array(
    'query' => 'select * from `posts` where `posts`.`category_id` in (?)',
    'bindings' => array(
      0 => '1'
    ),
    'time' => 1.11
  ),
  2 => array(
    'query' => 'select * from `comments` where `comments`.`post_id` in (?, ?)',
    'bindings' => array(
      0 => '1',
      1 => '3'
    ),
    'time' => 0.83
  )
)
 

 

As you can see we ‘ve got lot of data here with only 3 queries. Pretty neat.

But sometimes you don’t really want to load that much. If that’s the case and you only need comments related to the category through posts, then we can do this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
// app/models/Category.php
 
class Category extends Eloquent {
 
    public function posts()
    {
        return $this->hasMany('Post');
    }
 
    // prepare our new far relation:
    public function comments()
    {
        return $this->hasManyThrough('Comment', 'Post');
    }
 

 

So we can accomplish our goal as easily as:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 
[1] > Category::find(1)->comments->toArray();
array(
  0 => array(
    'id' => '1',
    'body' => 'This was awesome!',
    'active' => '1',
    'post_id' => '1',
    'category_id' => '1'
  ),
  1 => array(
    'id' => '2',
    'body' => 'Active comment 1',
    'active' => '1',
    'post_id' => '1',
    'category_id' => '1'
  ),
  2 => array(
    'id' => '3',
    'body' => 'Inactive comment 1',
    'active' => '0',
    'post_id' => '1',
    'category_id' => '1'
  ),
  3 => array(
    'id' => '4',
    'body' => 'Active comment 2',
    'active' => '1',
    'post_id' => '1',
    'category_id' => '1'
  ),
  4 => array(
    'id' => '6',
    'body' => 'Inactive comment 2',
    'active' => '0',
    'post_id' => '1',
    'category_id' => '1'
  )
)
 

This way we did not load posts, but saved a bit on the queries:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
[2] > DB::getQueryLog();
array(
  0 => array(
    'query' => 'select * from `categories` where `id` = ? limit 1',
    'bindings' => array(
      0 => 1
    ),
    'time' => 1.23
  ),
  1 => array(
    'query' => 'select `comments`.*, `posts`.`category_id` from `comments` inner join `posts` on `posts`.`id` = `comments`.`post_id` where `posts`.`category_id` = ?',
    'bindings' => array(
      0 => '1'
    ),
    'time' => 1.07
  )
)
 

 

Again Eloquent in action is quite impressive and soo easy to work with, ain’t it?!

Above examples could be forked to load only active comments for example.  How? Anyone?

It's only fair to share...Tweet about this on TwitterShare on RedditPin on PinterestShare on FacebookShare on Google+

Related Post

 Querying relations with Eloquent in Laravel 4 Laravel ORM Eloquent is brilliant! That being said, let's try to tackle very common questions that appear when you try to get related models lo...
Querying relations in Laravel: get Models where la... Another part of Querying relations in Laravel will cover such problem: I want to get SomeModels that have latest RelatedModel (hasMany) matchin...
Laravel – querying any level far relations w... Eloquent provides one Relation type for far related tables - hasManyThrough. However it works only with with cascade of hasOne/hasMany relations a...
Laravel – how to define and use Eloquent Glo... Laravel 4.2 introduced a new way of handling soft deletes by making use of traits and query builder macros wrapped in as a feature, that many people a...