Getting child objects from a DB::table query in Laravel

Question

I have 3 tables: Posts, Tags and Post_tag. The last one is just a pivot table:

Posts:

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title       | varchar(60)      | NO   |     | NULL    |                |
| created_at  | timestamp        | YES  |     | NULL    |                |
| updated_at  | timestamp        | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

Tags:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50)      | NO   |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

Post_tag:

+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| post_id    | int(10) unsigned | YES  | MUL | NULL    |       |
| tag_id     | int(10) unsigned | YES  | MUL | NULL    |       |
| created_at | timestamp        | YES  |     | NULL    |       |
| updated_at | timestamp        | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+

Models are correct.

Problem:

I'm getting this error in the posts/index.blade.php file:

Undefined property: stdClass::$tags
(View: D:\laragon\www\laravel\resources\views\posts\index.blade.php)

In the posts/index.blade.php I have this @foreach inside a @foreach ($posts as $post) to list the tags a post belongs to.

@foreach($post->tags as $tag)
    <a class="btn btn-sm btn-info" href="/tag/{{ $tag->id }}">
        <span>{{ $tag->name }}</span>
    </a>
@endforeach

The previous @foreach only works if the controller sends $posts = Post::all()

However I can only send to the view some rows with some conditions (Published, author id, etc.). So I have this in the controller:

$posts = DB::table('posts')
                ->orderBy('posts.created_at', 'desc')
                ->where('town_id', '=', $request->session()->get('citySelected')->id)
                ->paginate(10);

I even tried to set up the joins:

$posts = DB::table('posts')
                ->join('post_tag', 'posts.id', '=', 'post_tag.post_id')
                ->join('tags', 'post_tag.tag_id', '=', 'tags.id')
                ->orderBy('posts.created_at', 'desc')
                ->where('town_id', '=', $request->session()->get('citySelected')->id)
                ->paginate(10);

But neither works, make me think DB::table only brings the parent objects but not the child objects.

Any ideas? Thanks.

Alexey Mezenin Alexey Mezenin · Accepted Answer · 2018-01-04T10:30:54

You can load posts with related tags by using with method:

$posts = Post::with('tags')
    ->where('town_id', session('citySelected')->id)
    ->latest()
    ->paginate(10);

Then you'll be able to do this:

@foreach ($posts as $post)
    @foreach($post->tags as $tag)
        <a class="btn btn-sm btn-info" href="/tag/{{ $tag->id }}">
            <span>{{ $tag->name }}</span>
        </a>
    @endforeach
@endforeach

Getting child objects from a DB::table query in Laravel

2 Answers