Filament•4mo ago

How to add `created_at` column with `sortable()` to `BelongsToMany` RelationManager correctly

I have a RelationManager with a BelongsToMany relationship, and both the pivot table and the relationship table has a created_at column. I try to add the column to the table like this:

public function table(Table $table): Table
{
  return $table
    ->recordTitleAttribute('first_name')
    ->columns([
      Tables\Columns\TextColumn::make('full_name'),
      Tables\Columns\TextColumn::make('role'),
      Tables\Columns\TextColumn::make('created_at')->dateTime()
        ->dateTime('M j Y, H:i')
        ->sortable()
    ])
    //.......

public function table(Table $table): Table
{
  return $table
    ->recordTitleAttribute('first_name')
    ->columns([
      Tables\Columns\TextColumn::make('full_name'),
      Tables\Columns\TextColumn::make('role'),
      Tables\Columns\TextColumn::make('created_at')->dateTime()
        ->dateTime('M j Y, H:i')
        ->sortable()
    ])
    //.......

This shows a date column in my table as expected, but when I try to sort the table by the created_at column, I get this Ambiguous column error:

local.ERROR: SQLSTATE[42702]: Ambiguous column: 7 ERROR:  ORDER BY "created_at" is ambiguous
LINE 1: ...r_id" where "group_user"."group_id" = $1 order by "created_a...
                                                             ^ (Connection: pgsql, SQL: select "group_user"."group_id" as "pivot_group_id", "group_user"."user_id" as "pivot_user_id", "group_user"."role" as "pivot_role", "group_user"."is_creator" as "pivot_is_creator", "group_user"."current_meal_type_id" as "pivot_current_meal_type_id", "group_user"."created_at" as "pivot_created_at", "group_user"."updated_at" as "pivot_updated_at", "group_user".*, "users".* from "users" inner join "group_user" on "users"."id" = "group_user"."user_id" where "group_user"."group_id" = 4 order by "created_at" asc limit 10 offset 0) {"view":{"view":"/Users/kaspernowak/development/meinrad/meinrad_laravel/filament/packages/tables/resources/views/index.blade.php","data":[]},"userId":6,"exception":"[object] (Spatie\\LaravelIgnition\\Exceptions\\ViewException(code: 0): SQLSTATE[42702]: Ambiguous column: 7 ERROR:  ORDER BY \"created_at\" is ambiguous
LINE 1: ...r_id\" where \"group_user\".\"group_id\" = $1 order by \"created_a...

local.ERROR: SQLSTATE[42702]: Ambiguous column: 7 ERROR:  ORDER BY "created_at" is ambiguous
LINE 1: ...r_id" where "group_user"."group_id" = $1 order by "created_a...
                                                             ^ (Connection: pgsql, SQL: select "group_user"."group_id" as "pivot_group_id", "group_user"."user_id" as "pivot_user_id", "group_user"."role" as "pivot_role", "group_user"."is_creator" as "pivot_is_creator", "group_user"."current_meal_type_id" as "pivot_current_meal_type_id", "group_user"."created_at" as "pivot_created_at", "group_user"."updated_at" as "pivot_updated_at", "group_user".*, "users".* from "users" inner join "group_user" on "users"."id" = "group_user"."user_id" where "group_user"."group_id" = 4 order by "created_at" asc limit 10 offset 0) {"view":{"view":"/Users/kaspernowak/development/meinrad/meinrad_laravel/filament/packages/tables/resources/views/index.blade.php","data":[]},"userId":6,"exception":"[object] (Spatie\\LaravelIgnition\\Exceptions\\ViewException(code: 0): SQLSTATE[42702]: Ambiguous column: 7 ERROR:  ORDER BY \"created_at\" is ambiguous
LINE 1: ...r_id\" where \"group_user\".\"group_id\" = $1 order by \"created_a...

How do I make sure that the table uses the created_at column from the users table when sorting?

Solution:

You could try: ```php ->sortable(query: function (Builder $query, string $direction): Builder { return $query...

Jump to solution

12 Replies

toeknee•4mo ago

What is your relationship... what if you take off sortable() ?

nowakOP•4mo ago

If I take off sortable(), then nothing changes with the column data, but the sortable functionality (clickable column header) is removed. The shown created_at data is from my Users model. This RelationManager is on my GroupResource:

public static function getRelations(): array
{
  return [
    RelationManagers\UsersRelationManager::class,
  ];
}

public static function getRelations(): array
{
  return [
    RelationManagers\UsersRelationManager::class,
  ];
}

And the relevant setup and table of the UsersRelationManager looks like this:

<?php

namespace App\Filament\Resources\GroupResource\RelationManagers;

use Filament\Resources\RelationManagers\RelationManager;
use Filament\Tables;
use Filament\Tables\Table;

class UsersRelationManager extends RelationManager
{
  protected static string $relationship = 'users';

  public function table(Table $table): Table
  {
    return $table
      ->recordTitleAttribute('first_name')
      ->defaultSort('role', 'desc')
      ->columns([
        Tables\Columns\TextColumn::make('full_name'),
        Tables\Columns\TextColumn::make('role')
          ->label('Group Role'),
        Tables\Columns\TextColumn::make('created_at')->dateTime()
          ->dateTime('M j Y, H:i')
          ->sortable()
      ]);
  }
}

<?php

namespace App\Filament\Resources\GroupResource\RelationManagers;

use Filament\Resources\RelationManagers\RelationManager;
use Filament\Tables;
use Filament\Tables\Table;

class UsersRelationManager extends RelationManager
{
  protected static string $relationship = 'users';

  public function table(Table $table): Table
  {
    return $table
      ->recordTitleAttribute('first_name')
      ->defaultSort('role', 'desc')
      ->columns([
        Tables\Columns\TextColumn::make('full_name'),
        Tables\Columns\TextColumn::make('role')
          ->label('Group Role'),
        Tables\Columns\TextColumn::make('created_at')->dateTime()
          ->dateTime('M j Y, H:i')
          ->sortable()
      ]);
  }
}

bump

toeknee•4mo ago

by setting it to be created_at and sortable() should be fine Can you show your relationship for the users?

nowakOP•4mo ago

Do you mean the users relationship in my Group model? It looks like this:

/**
     * Get all of the users that belong to the group.
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function users()
    {
        return $this->belongsToMany(User::class, 'group_user')
                    ->withPivot('role', 'is_creator', 'current_meal_type_id') 
                    ->withTimestamps()
                    ->as('membership');
    }

/**
     * Get all of the users that belong to the group.
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function users()
    {
        return $this->belongsToMany(User::class, 'group_user')
                    ->withPivot('role', 'is_creator', 'current_meal_type_id') 
                    ->withTimestamps()
                    ->as('membership');
    }

setting it to be created_at and sortable() gives me the error I shared when I try to sort the table by created_at

toeknee•4mo ago

Do you understand whats happening? why the SQL error occurs? it's because there are multiple created_at basically, so part of it is setting is as created_at. But if the relationship is users it should be scoped to the relation. Unless you have a custom filter/relationship shorting by created_at. So try and find where the created_at is defined

nowakOP•4mo ago

@toeknee What I understand is that there is a created_at column in the users table and in the group_user pivot table, which is what is causing this ambiguous column error, as the sortable() method does not know whether to use the scoped users table or the group_user table. Am I wrong? The created_at on the users table is defined in the migration file with $table->timestamps(); And on the relationship in the Group model, the created_at column is defined by the ->withTimestamps() on the group_user pivot table.

toeknee•4mo ago

I suspect the problem is you have timestamps on the group_user privot as it shouldn't really matter when the pivot was added persay so when the pivot merges with the users it's not splitting the created_at which creates the ambiguious

nowakOP•4mo ago

Hmm.. But I want to keep track of when a user became a member of a group. Is there no way to explicitly choose the created_at from the users table?

Solution

toeknee•4mo ago

You could try:

    ->sortable(query: function (Builder $query, string $direction): Builder {
        return $query
            ->orderBy('users.created_at', $direction);
    })

    ->sortable(query: function (Builder $query, string $direction): Builder {
        return $query
            ->orderBy('users.created_at', $direction);
    })

nowakOP•4mo ago

Thank you @toeknee, that works like a charm! Is this something that could be considered a bug in the sortable method or the RelationManager? Or is it just how laravel works? I could imagine that this issue could occur for others in the future.

toeknee•4mo ago

Welcomes! It could be possibly... I'm not familiar with the inner workings too deep. It could be theroised that we should scope the query always with the model which would advoid the issue. But there could be other reasons. having created_at and updated_at is quite rare on a pivot tbh

nowakOP•4mo ago

Alright, well at least this thread would be here in case others experience the same issue.

Gaming

Programming

How to add `created_at` column with `sortable()` to `BelongsToMany` RelationManager correctly