Laravel Yajra DataTables 关系列排序与ID获取最佳实践

<?php

namespace App\DataTables;

use App\Models\Kabupaten;
use Yajra\DataTables\Html\Button;
use Yajra\DataTables\Html\Column;
use Yajra\DataTables\Services\DataTable;

class KabupatenDataTable extends DataTable
{
    /**
     * Build DataTable class.
     *
     * @param mixed $query Results from query() method.
     * @return \Yajra\DataTables\DataTableAbstract
     */
    public function dataTable($query)
    {
        return datatables()
            ->eloquent($query)
            ->addIndexColumn()
            ->addColumn('kordinat', function(Kabupaten $kabupaten) {
                // 这里的 $kabupaten 对象现在包含通过 JOIN 获取的 provinsi 属性
                return $kabupaten->koordinat_bujur . "," . $kabupaten->koordinat_lintang;
            })
            ->addColumn('action', 'kabupaten.action');
    }

    /**
     * Get query source of dataTable.
     *
     * @param \App\Models\Kabupaten $model
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function query(Kabupaten $model)
    {
        // 使用 leftJoin 明确连接 provinsi 表
        return Kabupaten::query()
            ->leftJoin('provinsi', 'provinsi.id', '=', 'kabupaten.provinsi_id')
            ->addSelect([
                'kabupaten.*', // 确保选择 kabupaten 表的所有列，包括其 ID
                'provinsi.provinsi as provinsi_provinsi' // 为关联列设置别名
            ]);
    }

    /**
     * Optional method if you want to use html builder.
     *
     * @return \Yajra\DataTables\Html\Builder
     */
    public function html()
    {
        return $this->builder()
                    ->setTableId('kabupaten-table')
                    ->columns($this->getColumns())
                    ->minifiedAjax()
                    ->dom('Bfrtip')
                    ->orderBy(1) // 根据实际列顺序调整
                    ->parameters([
                        'buttons' => ['excel'],
                    ])
                    ->buttons(
                        Button::make('create'),
                        Button::make('export'),
                        Button::make('print'),
                        Button::make('reset'),
                        Button::make('reload')
                    );
    }

    /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
            Column::make('DT_RowIndex')->title('No')->orderable(false)->searchable(false),
            // 使用别名定义列，并设置 name 属性以支持排序
            Column::make('provinsi_provinsi')->name('provinsi_provinsi')->title('Provinsi'),
            Column::make('status'),
            Column::make('kabupaten')->title('Nama kabupaten'),
            Column::make('ketinggian'),
            Column::make('kordinat')
                ->exportable(false)
                ->printable(false),
            Column::computed('action')
                ->exportable(false)
                ->printable(false),
        ];
    }

    /**
     * Get filename for export.
     *
     * @return string
     */
    protected function filename()
    {
        return 'Kabupaten_' . date('YmdHis');
    }
}

// ... (在 KabupatenDataTable 类的 getColumns 方法中)
protected function getColumns()
{
    return [
        Column::make('DT_RowIndex')->title('No')->orderable(false)->searchable(false),
        // 使用别名定义列，并设置 name 属性以支持排序
        Column::make('provinsi_provinsi')->name('provinsi_provinsi')->title('Provinsi'),
        Column::make('status'),
        Column::make('kabupaten')->title('Nama kabupaten'),
        Column::make('ketinggian'),
        Column::make('kordinat')
            ->exportable(false)
            ->printable(false),
        Column::computed('action')
            ->exportable(false)
            ->printable(false),
    ];
}

{{-- resources/views/kabupaten/action.blade.php --}}
<form id="delete_form" method="POST"> 
    @csrf
    @method('DELETE')
</form>
<a type="button" href="{{route('adm.kabupaten.show', ['id' => $id])}}" class="btn btn-just-icon btn-round btn-sm btn-info" data-toggle="tooltip" data-placement="bottom" title="detail">
    <i class="fa fa-eye"></i>
</a>
<a href="{{route('adm.kabupaten.edit', ['id' => $id])}}" class="btn btn-just-icon btn-round btn-sm btn-primary" data-toggle="tooltip" data-placement="bottom" title="ubah">
    <i class="fa fa-edit"></i>
</a>
<a href="#" data-url="{{ route('adm.kabupaten.destroy', $id) }}" class="btn btn-just-icon btn-round btn-sm btn-danger" onclick="return deleteConfirm(this);">
    <i class="fa fa-trash"></i>
</a>

<script>
    $(function() {
        $('[data-toggle="tooltip"]').tooltip();
    })
</script>