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
enen 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);
})