Select specific columns from relationship tables in Laravel

86

Working with multiple tables and their relationship is common. While combining results from multiple tables, we need to define the relationships between them. Laravel eloquent supports various relationships. After we define the relationships between multiple tables, accessing specific columns can be tricky. In this article, we will learn how we can access only selected columns records from the relationships table in Laravel.

Let us suppose we have a Article model with the one-to-many relationships to the comments table. The Article model looks like below:

<?php

namespace App\Models;

use App\Models\Comment;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Factories\HasFactory;

class Article extends Model
{
    use HasFactory;

    protected $fillable = ['title', 'content', 'status'];

    public function comments() {
        return $this->hasMany(Comment::class);
    }
}

The main thing to consider in the above code is the one-to-many relation with comments table. The comments table is a simple table with the below columns as defined in the migration.

// comments migration file
................
public function up()
{
    Schema::create('comments', function (Blueprint $table) {
        $table->id();
        $table->bigInteger('article_id');
        $table->string('name');
        $table->string('email');
        $table->text('comment');
        $table->timestamps();
    });
}

Now, we understand that articlestable is linked with the comments table. We can simply get the articles’ record with their respective comments as below.

Article::with('comments')->get();

But how can we only get the columns from the comments table? Well, the answer is we need to give the columns name while accessing the records.

Article::with('comments:id, name, comment')->get();

Please note that we always need to define the idfield as stated in the official documentation – “When using this feature, you should always include the id column and any relevant foreign key columns in the list of columns you wish to retrieve.

The above method works perfectly fine, but if you also want to try other methods, below is an alternative. We will need to define the subquery with the relationships as in the below example.

<?php

Article::query()->with(['comments' => function ($q) {
        $q->select('id', 'name', 'comments');
    }])
    ->get()

This is all about accessing specific columns from the relationship tables. What are your thoughts? Don’t forget to drop your comments below.

Read More Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.