Filament•7mo ago

Many-to-Many relationship - get name from third table

Hi everyone, I'm new in Laravel/Filament 👀 So maybe my question is very trivial. So I have subjects and courses tables, which have many-to-many relationship (there is a course_subject pivot table). In course table there is an another foreign key - department_id. I want to on Subject resource page on Select and Column Field get course with Department name from departments table. On second pic 1, 2 are courses: But I want to show like Department name + Course name. Thanks

25 Replies

ArshavirOP•7mo ago

I think I found solution:) I just needed to call courses.department.name courses.department.name only get Department name I need also concatenate Course name too, and also need to get Department name + Course name in Select

Sjoerd24•7mo ago

how does your model looks? Subject (model)?

ArshavirOP•7mo ago

Subject Model

public function chair()
    {
        return $this->belongsTo(Chair::class);
    }

    public function courses()
    {
        return $this->belongsToMany(Course::class);
    }

Course Model

public function department()
    {
        return $this->belongsTo(Department::class);
    }

    public function subjects()
    {
        return $this->belongsToMany(Subject::class);
    }

Trying with this

Tables\Columns\TextColumn::make('courses.department.name')
                    ->label('Courses')
                    ->formatStateUsing(function ($state, Subject $subject) {
                        return $subject->courses;
                    })
                    ->badge()
                    ->sortable(),

but getting only last json

ArshavirOP•7mo ago

Rome•7mo ago

Try ->formatStateUsing(fn (Model $record) => "{$record->courses->name} - {$record->courses->department->name}")

ArshavirOP•7mo ago

Tried, but problem is that I get same json array like in picture mentioned above also I need to call [0] like this ->formatStateUsing(fn (Model $record) => "{$record->courses[0]->course} - {$record->courses[0]->department->name}")

ArshavirOP•7mo ago

Rome•7mo ago

Maybe do a foreach inside and return each course? (dont know if it works like this)

->formatStateUsing(function (Model $record) {
    foreach($record->courses as $course) {
        return "{$course->name} - {$course->department->name}");
    }
}

ArshavirOP•7mo ago

No it not help, same thing In one word I want to concatenate two strings like in native PHP 'courses.department.name'.' '.'courses.course'.' term'

Vp•7mo ago

Since your relationship is belongsToMany yes you'll get array of data then you have to do [0] You can check this (https://laravel.com/docs/11.x/eloquent-relationships#has-one-of-many) to return latest/oldest of many and you don't need to check [0] anymore.

ArshavirOP•7mo ago

Ok, but why I get same array? when using formatStateUsing

Vp•7mo ago

Not sure, can you run this below code and check the output:

Subject::with([
    'courses' => fn ($q) => $q->with('department')
])
->whereId(1)
->get()
->dd();

Subject::with([
    'courses' => fn ($q) => $q->with('department')
])
->whereId(1)
->get()
->dd();

Let's see how many courses your subject had, maybe this explains

ArshavirOP•7mo ago

I checked, it get all courses, but when using formatStateUsing to concat, then it gets same course, see pictures above

Tables\Columns\TextColumn::make('courses.department.name')
                    ->label('Courses')
                    ->formatStateUsing(fn (Model $record) =>
                    "{$record->courses[0]->course} - {$record->courses[0]->department->name}")
                    ->badge()
                    ->sortable(),

with this

ArshavirOP•7mo ago

Tables\Columns\TextColumn::make('courses.department.name')
                    ->label('Courses')
                    // ->formatStateUsing(fn (Model $record) =>
                    // "{$record->courses[0]->course} - {$record->courses[0]->department->name}")
                    ->badge()
                    ->sortable(),

with this

ArshavirOP•7mo ago

Should be General Medicine 1, General Medicine 2, Stomatology 1

Vp•7mo ago

Does your department name contain General Medicine 1 etc because courses.department.name will display the department name for all your courses

ArshavirOP•7mo ago

see this department name is General Medicine Course is 1 which belongsTo Department General Medicine, etc...

Vp•7mo ago

Without using ->formatState.... it's displaying correct, try changing courses.name to see the difference because if you use courses.department.name then it'll display only department name for all courses This one is form, but now we're talking about table 🧐

ArshavirOP•7mo ago

courses.name will return course name only - 1 but course table have foreign department_id I want to get Department name from departments table and course name from courses table Deapartment + Course

Vp•7mo ago

Use courses.name and try like below:

->formatStateUsing(function ($state) {
    return "{$state->department->name} - {$state}"
})

// or like this (not sure, not tested)
->formatStateUsing(function ($state, Model $record) {
    $record->each(function ($q) use ($state) {
        return "{$q->department->name} - {$state}"
    })
})

->formatStateUsing(function ($state) {
    return "{$state->department->name} - {$state}"
})

// or like this (not sure, not tested)
->formatStateUsing(function ($state, Model $record) {
    $record->each(function ($q) use ($state) {
        return "{$q->department->name} - {$state}"
    })
})

ArshavirOP•7mo ago

no luck

Vp•7mo ago

dd($record) inside format state, what did you get?

ArshavirOP•7mo ago

Solved with this try

Tables\Columns\TextColumn::make('courses')
                    ->label('Courses')
                    ->formatStateUsing(fn (Model $state) =>
                    "{$state->department->name} - {$state->course}")

Tables\Columns\TextColumn::make('courses')
                    ->label('Courses')
                    ->formatStateUsing(fn (Model $state) =>
                    "{$state->department->name} - {$state->course}")

Now will try same thing with Select Form Please note I named Course name as course With Select

Forms\Components\Select::make('course_id')
                    ->relationship('courses', 'id')
                    ->getOptionLabelFromRecordUsing(fn (Model $state) =>
                    "{$state->department->name} - {$state->course}")

Forms\Components\Select::make('course_id')
                    ->relationship('courses', 'id')
                    ->getOptionLabelFromRecordUsing(fn (Model $state) =>
                    "{$state->department->name} - {$state->course}")

Gaming

Programming

Many-to-Many relationship - get name from third table