Filament•11mo ago

relationship sorting in table

Tables\Columns\TextColumn::make('firstCategory.name')
->sortable(query: fn ($query, $direction) => 
  $query->with('firstCategory')
  ->orderBy('firstCategory.order_column', $direction)
),

Tables\Columns\TextColumn::make('firstCategory.name')
->sortable(query: fn ($query, $direction) => 
  $query->with('firstCategory')
  ->orderBy('firstCategory.order_column', $direction)
),

`SQLSTATE[42S22]: Column not found: 1054 Unknown column 'firstCategory.order_column' in 'order clause'`

`SQLSTATE[42S22]: Column not found: 1054 Unknown column 'firstCategory.order_column' in 'order clause'`

7 Replies

DrByte•11mo ago

What's the full generated SQL?

coreanOP•11mo ago

SELECT
  *
FROM
  `product_2_categories`
ORDER BY
  `firstCategory`.`order_column` ASC
limit
  10 OFFSET 0

SELECT
  *
FROM
  `product_2_categories`
ORDER BY
  `firstCategory`.`order_column` ASC
limit
  10 OFFSET 0

DrByte•11mo ago

It looks like you don't have any relationships set up between the tables. If you have the relationship set up, why not just specify the sort as firstCategory.order_column? example: On a table for a resource for a Notes model that has a BelongsTo relationship named user to the Users table, I can sort by user name when listing the notes in the table:

   ->defaultSort('user.name')

   ->defaultSort('user.name')

coreanOP•11mo ago

The relationship is currently set up.

    public function firstCategory(): BelongsTo
    {
        return $this->belongsTo(ProductFirstCategory::class, 'category_1_id');
    }

    public function firstCategory(): BelongsTo
    {
        return $this->belongsTo(ProductFirstCategory::class, 'category_1_id');
    }

Dennis Koch•11mo ago

You can't use with() and orderBy() on the same data With is an Eloquent construct that loads relationship in another query.

coreanOP•11mo ago

Tables\Columns\TextColumn::make('firstCategory.name')

Tables\Columns\TextColumn::make('firstCategory.name')

displayed in table builder

Dennis Koch•11mo ago

You probably want a JOIN clause Sure. It fetches your category and then displays it. But that might be 2 queries. Just because it's displayed in the table, that doesn't mean it's loaded with the same query

Gaming

Programming

relationship sorting in table