Summarizers\Sum Unable to count BelongsToMany intermediate table fields correctly

[yzxh24]

Package

filament/filament

Package Version

v3.2.66

Laravel Version

v10.48.7

Livewire Version

No response

PHP Version

PHP 8.2.0

Problem description

When BelongsToMany is used for intermediate table, summarize() cannot sum the intermediate table fields correctly.

In mysql , The error message looks like this:

In sqlite, the error message looks like this:

Expected behavior

I want summarize() to count the data correctly.

Steps to reproduce

I have created three tables as follows:

exams:
  id
  title
  score

questions:
  id
  title

exam_questions:
  id
  exam_id
  question_id
  score
  sort_value

model as follows:

class Exam extends Model {
  use HasFactory, HasUlids;

  public function questions(): BelongsToMany
  {
      return $this->belongsToMany(Question::class, 'exam_questions', 'exam_id', 'question_id')
          ->withPivot('score', 'sort_value')
          ->orderByPivot('sort_value', 'desc');
  }
}

class Question extends Model {
  use HasFactory, HasUlids;

  public function exams(): BelongsToMany
  {
      return $this->belongsToMany(Exam::class, 'exam_questions', 'question_id', 'exam_id')
          ->withPivot('score', 'sort_value');
  }
}

class ExamQuestion extends Pivot {
    use HasFactory, HasUlids;
}

When I use RelationManager to manage associations, I get an error when using Tables\Columns\Summarizers\Sum::make() in table():

public function table(Table $table): Table
{
    return $table->->columns([
      Tables\Columns\TextInputColumn::make('pivot.sort_value')
        ->label(__('model.sort_value'))
        ->updateStateUsing(function ($record, $state) {
            return $record->pivot->update(['sort_value' => $state]);
        })
        ->summarize(Tables\Columns\Summarizers\Sum::make())
    ]);
}

Reproduction repository

https://github.com/yzxh24/filament-issue

Relevant log output

No response

[jeremy-at-evosus]

Same kind of question but on a polymorphic relationship.

       public function ratings()
       {
            return $this->morphMany(Rating::class, 'rateable');
       }

        TextColumn::make('ratings')
            ->label(__('Votes'))
            ->sortable();

[dmitry-udod]

@yzxh24 Hi! Try to remove pivot. prefix here

And after that, it works well for me.

https://github.com/filamentphp/filament/assets/4639175/095e8be8-3486-44fd-b94d-f61db96bd7c2

[yzxh24]

@yzxh24 Hi! Try to remove pivot. prefix here

And after that, it works well for me.

Screencast.2024-04-22.10.55.40.mp4

@dmitry-udod Hi,my friend, thanks for your reply.

It is indeed correct to change it the way you said.

However, there is something wrong with my previous code, the questions table also has a score column for the question scores. I have uploaded the code to the library https://github.com/yzxh24/filament-issue

After I added this column the sum came out correctly, but the score shown on each row of the table is for the questions table.

The final writeup should be something like this:

Tables\Columns\TextColumn::make('pivot.score')
    ->summarize(Tables\Columns\Summarizers\Sum::make())
        /// Here we specify the score
        ->using(fn (Builder $query): string => $query->sum('score'))
    )
    ->label('Score')

After writing it this way it passes on sqlite, but when I switch to mysql database it produces the following error:

I am using mysql 5.7.39, is there any other database settings I need to make to eliminate this problem?

[dmitry-udod]

@yzxh24 Hi! Yes, there is a problem with MySQL, and it seems to occur because MySQL doesn't know which column to choose for aggregation, given that both 'exam_questions.id' and 'questions.id' are present.

As a quick fix solution you can change your QuestionsRelationManager.php from

$query->sum('score'))

to

 $this->getOwnerRecord()->questions()->sum('questions.score'))

[yzxh24]

@dmitry-udod Yes, just add the table name, since what I need sum for is the score of the exam_qustions table, so it should be changed to:

Tables\Columns\TextInputColumn::make('pivot.score')
     ->summarize(Tables\Columns\Summarizers\Sum::make())
        /// The exam_questions table is used here
         ->using(fn (Builder $query): string => $this->getOwnerRecord()->questions()->sum('exam_questions.score'))
     )

Testing on mysql has worked fine. Thanks again for your help!