Filament•4mo ago

HasMany Table Relationship pivot/group RelationManager

Solution:

I got this working: ``` protected function getTableQuery() return $this->record->submissions()->groupBy('session_id')->getQuery(); }...

Jump to solution

8 Replies

lmtcOP•3mo ago

Bumping this - I've currently done it as a custom view - but would prefer to have it as a normal table

toeknee•3mo ago

Please example more..... I dont get field_name and form_id just remove them...

lmtcOP•3mo ago

So I have a form resource which has an id, I generate a form and store each field name and label against a session id, I then want to group it by session id and have a column for first_name and last_name with the values under it I have this working as a custom view but I'd rather use the default if it's at all possible

lmtcOP•3mo ago

e.g

toeknee•3mo ago

Id need to see the table/form you are suing to understand it better

lmtcOP•3mo ago

The custom view? This is my list submissions page:

public function mount(int | string $record): void
    {
        $this->record = $this->resolveRecord($record);
        $this->formSubmissions = $this->getPivotedFormSubmissions();
    }

    public function getPivotedFormSubmissions()
    {
        // Fetch all form submissions
        $submissions = DB::table('form_submissions')
            ->where('form_id', $this->record->id)
            ->get();

        // Group by session_id
        $grouped = $submissions->groupBy('session_id');

        // Extract all distinct field names
        $fieldNames = $submissions->pluck('field_name')->unique();

        // Pivot data using collections
        $pivoted = $grouped->map(function ($items) use ($fieldNames) {
            $pivot = ['session_id' => $items->first()->session_id];

            foreach ($fieldNames as $field) {
                $pivot[$field] = $items->firstWhere('field_name', $field)->field_value ?? null;
            }

            return $pivot;
        });

        return $pivoted->values(); // Resetting the keys
    }

public function mount(int | string $record): void
    {
        $this->record = $this->resolveRecord($record);
        $this->formSubmissions = $this->getPivotedFormSubmissions();
    }

    public function getPivotedFormSubmissions()
    {
        // Fetch all form submissions
        $submissions = DB::table('form_submissions')
            ->where('form_id', $this->record->id)
            ->get();

        // Group by session_id
        $grouped = $submissions->groupBy('session_id');

        // Extract all distinct field names
        $fieldNames = $submissions->pluck('field_name')->unique();

        // Pivot data using collections
        $pivoted = $grouped->map(function ($items) use ($fieldNames) {
            $pivot = ['session_id' => $items->first()->session_id];

            foreach ($fieldNames as $field) {
                $pivot[$field] = $items->firstWhere('field_name', $field)->field_value ?? null;
            }

            return $pivot;
        });

        return $pivoted->values(); // Resetting the keys
    }

Solution

lmtc•3mo ago

I got this working:

protected function getTableQuery()
return $this->record->submissions()->groupBy('session_id')->getQuery();
}

protected function getTableColumns(): array
{
$fieldNames = $this->record->submissions()
    ->pluck('field_name')
    ->unique();

$columns = [];

foreach ($fieldNames as $field) {
    $columns[] = TextColumn::make($field)
        ->label($field)
        ->getStateUsing(fn($record) => $this->getFieldValue($record, $field));
}

return $columns;
}

protected function getFieldValue(FormSubmissions $record, string $field)
{
return $record->where('session_id', $record->session_id)
    ->where('field_name', $field)
    ->value('field_value');
}

public function table(Table $table): Table
{
return $table
    ->query($this->getTableQuery())
    ->columns($this->getTableColumns())
    ->actions([
        DeleteAction::make()
            ->action(function (FormSubmissions $record) {
                FormSubmissions::where('session_id', $record->session_id)
                    ->where('form_id', $record->form_id)
                    ->delete();
            })
            ->requiresConfirmation()
            ->label('Delete'),
    ])
    ->bulkActions([
        BulkAction::make('deleteAll')
            ->action(function (Collection $records) {
                // Retrieve unique session IDs from the selected records
                $sessionIds = $records->pluck('session_id')->unique();

                // Delete all submissions related to these session IDs
                FormSubmissions::whereIn('session_id', $sessionIds)->delete();
            })
            ->requiresConfirmation()
            ->label('Delete All'),
    ]);
}

protected function getTableQuery()
return $this->record->submissions()->groupBy('session_id')->getQuery();
}

protected function getTableColumns(): array
{
$fieldNames = $this->record->submissions()
    ->pluck('field_name')
    ->unique();

$columns = [];

foreach ($fieldNames as $field) {
    $columns[] = TextColumn::make($field)
        ->label($field)
        ->getStateUsing(fn($record) => $this->getFieldValue($record, $field));
}

return $columns;
}

protected function getFieldValue(FormSubmissions $record, string $field)
{
return $record->where('session_id', $record->session_id)
    ->where('field_name', $field)
    ->value('field_value');
}

public function table(Table $table): Table
{
return $table
    ->query($this->getTableQuery())
    ->columns($this->getTableColumns())
    ->actions([
        DeleteAction::make()
            ->action(function (FormSubmissions $record) {
                FormSubmissions::where('session_id', $record->session_id)
                    ->where('form_id', $record->form_id)
                    ->delete();
            })
            ->requiresConfirmation()
            ->label('Delete'),
    ])
    ->bulkActions([
        BulkAction::make('deleteAll')
            ->action(function (Collection $records) {
                // Retrieve unique session IDs from the selected records
                $sessionIds = $records->pluck('session_id')->unique();

                // Delete all submissions related to these session IDs
                FormSubmissions::whereIn('session_id', $sessionIds)->delete();
            })
            ->requiresConfirmation()
            ->label('Delete All'),
    ]);
}

toeknee•3mo ago

Good work

Gaming

Programming

HasMany Table Relationship pivot/group RelationManager