arbuzik
polymorphic sorting by parent
What am i missing here? i am trying to be able to sort a table by the parent's title (a json column where only the
en field interests me) but no success yet.
Tables\Columns\TextColumn::make('subclusterable.title')
->sortable(true, function (Builder $query, string $direction) {
$query->leftJoin('clusters', function ($join) {
$join->on('sub_clusters.subclusterable_id', '=', 'clusters.id')
->where('sub_clusters.subclusterable_type', 'App\Models\Cluster');
})
->leftJoin('pillars', function ($join) {
$join->on('sub_clusters.subclusterable_id', '=', 'pillars.id')
->where('sub_clusters.subclusterable_type', 'App\Models\Pillar');
})
->leftJoin('assets', function ($join) {
$join->on('sub_clusters.subclusterable_id', '=', 'assets.id')
->where('sub_clusters.subclusterable_type', 'App\Models\Asset');
})
->leftJoin('assets_categories', function ($join) {
$join->on('sub_clusters.subclusterable_id', '=', 'assets_categories.id')
->where('sub_clusters.subclusterable_type', 'App\Models\AssetsCategory');
})
->orderByRaw("COALESCE(
JSON_UNQUOTE(JSON_EXTRACT(clusters.title, '$.en')),
JSON_UNQUOTE(JSON_EXTRACT(pillars.title, '$.en')),
JSON_UNQUOTE(JSON_EXTRACT(assets.title, '$.en')),
JSON_UNQUOTE(JSON_EXTRACT(assets_categories.title, '$.en'))
) ".$direction);
})
Tables\Columns\TextColumn::make('subclusterable.title')
->sortable(true, function (Builder $query, string $direction) {
$query->leftJoin('clusters', function ($join) {
$join->on('sub_clusters.subclusterable_id', '=', 'clusters.id')
->where('sub_clusters.subclusterable_type', 'App\Models\Cluster');
})
->leftJoin('pillars', function ($join) {
$join->on('sub_clusters.subclusterable_id', '=', 'pillars.id')
->where('sub_clusters.subclusterable_type', 'App\Models\Pillar');
})
->leftJoin('assets', function ($join) {
$join->on('sub_clusters.subclusterable_id', '=', 'assets.id')
->where('sub_clusters.subclusterable_type', 'App\Models\Asset');
})
->leftJoin('assets_categories', function ($join) {
$join->on('sub_clusters.subclusterable_id', '=', 'assets_categories.id')
->where('sub_clusters.subclusterable_type', 'App\Models\AssetsCategory');
})
->orderByRaw("COALESCE(
JSON_UNQUOTE(JSON_EXTRACT(clusters.title, '$.en')),
JSON_UNQUOTE(JSON_EXTRACT(pillars.title, '$.en')),
JSON_UNQUOTE(JSON_EXTRACT(assets.title, '$.en')),
JSON_UNQUOTE(JSON_EXTRACT(assets_categories.title, '$.en'))
) ".$direction);
})
3 replies
sorting by polymorphed parent's field
hello everyone. i have a model called SubCluster which can be morphed into several models like this -
now, for example, a Cluster (one of the potential parents) model is morphedMany to subcluster like this -
now, in filament, i want to be able to sort by the parent's title as well -
public function subclusterable()
{
return $this->morphTo();
}
public function subclusterable()
{
return $this->morphTo();
}
public function subClusters()
{
return $this->morphMany(SubCluster::class, 'subclusterable');
}
public function subClusters()
{
return $this->morphMany(SubCluster::class, 'subclusterable');
}
public static function table(Table $table): Table
{
return $table
->columns([
Tables\Columns\TextColumn::make('title')
->sortable(true, function (Builder $query, string $direction) {
$query->orderByRaw("JSON_UNQUOTE(JSON_EXTRACT(title, '$.en')) $direction");
})
->searchable(query: function (Builder $query, string $search): Builder {
return $query
->where('title', 'like', "%{$search}%")
->orWhereRaw('LOWER(`title`) LIKE LOWER(?)', ["%{$search}%"]);
}),
Tables\Columns\TextColumn::make('subclusterable.title')
->label('Parent'),
Tables\Columns\TextColumn::make('author.name')
->sortable(),
Tables\Columns\TextColumn::make('created_at')
->dateTime()
->sortable()
->toggleable(isToggledHiddenByDefault: true),
Tables\Columns\TextColumn::make('updated_at')
->dateTime()
->sortable()
->toggleable(isToggledHiddenByDefault: true),
])
}
public static function table(Table $table): Table
{
return $table
->columns([
Tables\Columns\TextColumn::make('title')
->sortable(true, function (Builder $query, string $direction) {
$query->orderByRaw("JSON_UNQUOTE(JSON_EXTRACT(title, '$.en')) $direction");
})
->searchable(query: function (Builder $query, string $search): Builder {
return $query
->where('title', 'like', "%{$search}%")
->orWhereRaw('LOWER(`title`) LIKE LOWER(?)', ["%{$search}%"]);
}),
Tables\Columns\TextColumn::make('subclusterable.title')
->label('Parent'),
Tables\Columns\TextColumn::make('author.name')
->sortable(),
Tables\Columns\TextColumn::make('created_at')
->dateTime()
->sortable()
->toggleable(isToggledHiddenByDefault: true),
Tables\Columns\TextColumn::make('updated_at')
->dateTime()
->sortable()
->toggleable(isToggledHiddenByDefault: true),
])
}
2 replies