Tables with filter by year

[markplus]

I need to know if I can filter the data by year, I would like a table with only data from a given year in it, with serverside I don't know how to set this up, I usually create a $ticket2023 = Ticket::whereYear(' created_at', Carbon::now()->year(2023))->count(); and I see with foreach the values I want, but I would like to do it with a TicketsDataTable.php table but I still don't understand how to filter the data as I want, I would like to do something like this but I don't know how to do it. Where and how can I insert something like this to get the result I want?

<tbody>
     @foreach ($lug2023 as $lug)
        <tr>
            <td>{{$lug->id}}</td>
            <td>{{date('Y', strtotime($lug->time_stamp))}}</td>
            <td>{{date('m', strtotime($lug->time_stamp))}}</td>
            <td>{{date('d', strtotime($lug->time_stamp))}}</td>
            <td>{{date('H', strtotime($lug->time_stamp))}}</td>
            <td>{{$lug->stato}}</td>
        </tr>
     @endforeach
</tbody>

<?php

namespace App\DataTables;

use App\Models\Ticket;
use Illuminate\Database\Eloquent\Builder as QueryBuilder;
use Yajra\DataTables\EloquentDataTable;
use Yajra\DataTables\Html\Builder as HtmlBuilder;
use Yajra\DataTables\Html\Button;
use Yajra\DataTables\Html\Column;
use Yajra\DataTables\Html\Editor\Editor;
use Yajra\DataTables\Html\Editor\Fields;
use Yajra\DataTables\Html\Editor\Fields\File;
use Yajra\DataTables\Html\SearchPane;
use Yajra\DataTables\Services\DataTable;

class TicketsLogsDataTable extends DataTable
{
    /**
     * Build the DataTable class.
     *
     * @param QueryBuilder $query Results from query() method.
     */
    public function dataTable(QueryBuilder $query): EloquentDataTable
    {
        return (new EloquentDataTable($query))->setRowId('id');
    }

    /**
     * Get the query source of dataTable.
     */
    public function query(Ticket $model): QueryBuilder
    {
        return $model->newQuery();
    }

    /**
     * Optional method if you want to use the html builder.
     */
    public function html(): HtmlBuilder
    {
        return $this->builder()
                    ->setTableId('tickets-table')
                    ->columns($this->getColumns())
                    ->minifiedAjax()
                    ->orderBy(1)
                    ->selectStyleSingle()
                    ->serverSide(true)
                    ->processing(true)
                    ->stateSave(true)
                    ->select(true)
                    ->fixedHeader(true)
                    ->deferRender(true)
                    ->orderCellsTop(true)
                    ->lengthChange(true)
                    ->autoWidth(false)
                    ->responsive(true)
                    ->editors([
                        Editor::make()
                              ->fields([
                                  Fields\Text::make('id'),
                                  Fields\Text::make('time_stamp'),
                                  Fields\Text::make('stato'),
                              ]),
                        Editor::make('upload')
                        ->fields([
                            File::make('csv')
                            ->label('Carica File:')
                            ->ajax("function(files) {
                                Papa.parse(files[0], {
                                    header: true,
                                    skipEmptyLines: true,
                                    complete: function (results) {
                                        if ( results.errors.length ) {
                                            LaravelDataTables['tickets-table-upload'].field('csv').error( 'CSV parsing error: '+ results.errors[0].message );
                                        }
                                        else {
                                            LaravelDataTables['tickets-table-upload'].close();
                                            $.fn.uploadCallback(LaravelDataTables['tickets-table-editor'], results.data, results.meta.fields)
                                        }
                                    }
                                });
                            }"),
                        ]),
                    ])
                    ->buttons([
                        Button::make('create')->editor('editor'),
                        Button::make('edit')->editor('editor'),
                        Button::make('remove')->editor('editor'),
                        Button::raw()
                        ->editor('upload')
                        ->text('Importa')
                        ->action("tickets_logs-table['tickets_logs-table-uploads'].create({title: 'Importa'});"),
                        Button::make('excel'),
                        Button::make('csv'),
                        Button::make('pdf'),
                        Button::make('print'),
                        Button::make('reset'),
                        Button::make('reload'),
                    ])
                    ->parameters([
                        'dom' => '<PBfrt<t>lip>',
                        'language' => ['url' => url('vendors/it-IT.json')],
                    ]);
    }

    /**
     * Get the dataTable columns definition.
     */
    public function getColumns(): array
    {
        return [
            Column::make('id'),
            Column::make('time_stamp'),
            Column::make('stato'),
        ];
    }

    /**
     * Get the filename for export.
     */
    protected function filename(): string
    {
        return 'Tickets_' . date('YmdHis');
    }
}

• Linux
• PHP Version 8.2
• Laravel Version 10.10
• Laravel-Datatables Version 10.1

[yajra]

You can just add a conditional where clause in the query:

    public function query(Ticket $model): QueryBuilder
    {
        return $model->newQuery()->when(request('year'), fn($q, $year) => $q->whereYear('field', $year));
    }