Handling timestamps

[XaphanBael]

I don't know if it's just me, but the grid filtering seems to fail when using timestamps. For example, i have a database field named 'test_date', which has a timestamp value of 2015-12-14 13:23:54 the grid i use has the javascript datepicker from example #4.

When attempting to filter, it doesn;t really do anything, and also blocks the functionality of other filter fields such as username dropdowns.

To test this, i created a test database table, with test model and controller, however this time instead of using a timestamp field i only used a data field, and so far that seems to work like it should.

[Nayjest]

Hi! What DBMS was used? What filtering operation was used? If you use 'eq' operation, then it will not work with timestamps unlike 'gt', 'lt' operations. Following query demonstrates it:

select now() = '2015-12-14', now() > '2015-12-14', now() < '2015-12-14'

Results: 0,1,0 The reason of this behavior is converting date string to timestamp for comparison, and of course something like '2015-12-14 13:59' will not equal to '2015-12-14 00:00'.

So, you need to convert timestamp to date:

select date(now()) = '2015-12-14', date(now()) > '2015-12-14', date(now()) < '2015-12-14'

Results: 1,0,0

It can be done using something like following code:

$filter->setFilteringFunc(function ($value, EloquentDataProvider $dp) {
    $dp->getBuilder()->where(DB::raw('date(some_timestamp)'), '=', $value);
});

[XaphanBael]

I use laravels eloquent to load the data (it's all in one table). Here is the code i use:

public function quotationNew()
{
    $query = (new Quotation)
        ->newQuery()
        ->where('status_forecast', 1)
        ->orWhere('status_forecast', 5);

    $grid = new Grid(
        (new GridConfig)
            ->setDataProvider(
                new EloquentDataProvider($query)
            )
            ->setName('QuotationNew')
            ->setPageSize(25)
            ->setColumns([
                (new FieldConfig)
                    ->setName('created_by_company_abbr')
                    ->setLabel('Country')
                    ->setSortable(true)
                    ->addFilter(
                        (new SelectFilterConfig)
                            ->setOptions((new Quotation)->getCountries())
                    )
                ,
                (new FieldConfig)
                    ->setName('serial')
                    ->setLabel('Sales quot.')
                    ->setSortable(true)
//                        ->addFilter(
//                            (new FilterConfig)
//                                ->setOperator(FilterConfig::OPERATOR_LIKE)
//                        )
                ,
                (new FieldConfig)
                    ->setName('c_name_0_1_2')
                    ->setLabel('Organisation')
                    ->setSortable(true)
//                        ->addFilter(
//                            (new FilterConfig)
//                                ->setOperator(FilterConfig::OPERATOR_LIKE)
//                        )
                ,
                (new FieldConfig)
                    ->setName('keywords')
                    ->setLabel('Keywords')
                    ->setSortable(true)
//                        ->addFilter(
//                            (new FilterConfig)
//                                ->setOperator(FilterConfig::OPERATOR_LIKE)
//                        )
                ,
                (new FieldConfig)
                    ->setName('created_date')
                    ->setLabel('Created')
                    ->setSortable(true)
                    ->setCallback(function ($val) {
                        return $val->format('Y-m-d');
                    })
                ,
                (new FieldConfig)
                    ->setName('date_possible')
                    ->setLabel('Date possible')
                    ->setSortable(true)
                    ->setCallback(function ($val) {
                        return $val->format('Y-m-d');
                    })
                ,
                (new FieldConfig)
                    ->setName('currency_company')
                    ->setLabel('Cur.')
                    ->setSortable(true)
                ,
                (new FieldConfig)
                    ->setName('total_sales_ex_vat')
                    ->setLabel('Total excl. vat')
                    ->setSortable(true)
                    ->setCallback(function ($val) {
                        return number_format($val, 2, ',', '.');
                    })
//                        ->addFilter(
//                            (new FilterConfig)
//                                ->setOperator(FilterConfig::OPERATOR_LIKE)
//                        )
                ,
                (new FieldConfig)
                    ->setName('total_margin_cc')
                    ->setLabel('Margin')
                    ->setSortable(true)
                    ->setCallback(function ($val) {
                        return number_format($val, 2, ',', '.');
                    })
//                        ->addFilter(
//                            (new FilterConfig)
//                                ->setOperator(FilterConfig::OPERATOR_LIKE)
//                        )
                ,
                (new FieldConfig)
                    ->setName('status_forecast')
                    ->setLabel('Status')
                    ->setSortable(true)
                    ->setCallback(function ($val) {
                        switch($val)
                        {
                            case 1:
                                return "Open";
                            case 2:
                                return "Delay";
                            case 3:
                                return "Missed";
                            case 4:
                                return "Order";
                            case 5:
                                return "New quotation";
                            default:
                                return "Unknown";
                        }
                    })
//                        ->addFilter(
//                            (new SelectFilterConfig)
//                                ->setOptions((new Quotation)->getStatuses())
//                        )
                ,
                (new FieldConfig)
                    ->setName('stage')
                    ->setLabel('Stage')
                    ->setSortable(true)
                    ->setCallback(function ($val) {
                        switch($val)
                        {
                            case 1:
                                return "Prospecting";
                            case 2:
                                return "Budgetary";
                            case 3:
                                return "Upside";
                            case 4:
                                return "Commit";
                            case 7:
                                return "Lost";
                            case 8:
                                return "(New) quotation";
                            default:
                                return "Unknown";
                        }
                    })
                ,
                (new FieldConfig)
                    ->setName('c_name_dep_company')
                    ->setLabel('Sales exec.')
                    ->setSortable(true)
                    ->addFilter(
                        (new SelectFilterConfig)
                            ->setOptions((new Quotation)->getSalesExecutives())
                    )
                ,
                (new FieldConfig)
                    ->setName('source')
                    ->setLabel('Source')
                    ->setSortable(true)
                    ->setCallback(function ($val) {
                        switch($val)
                        {
                            case 2:
                                return "Cold call";
                            case 5:
                                return "E-mail";
                            case 6:
                                return "Employee";
                            case 8:
                                return "Existing customer";
                            case 12:
                                return "Other";
                            case 13:
                                return "Partner";
                            case 15:
                                return "Self generated";
                            case 17:
                                return "Website";
                            default:
                                return "Unknown";
                        }
                    })
//                        ->addFilter(
//                            (new FilterConfig)
//                                ->setOperator(FilterConfig::OPERATOR_LIKE)
//                        )
                ,
            ])
            ->setComponents([
                (new THead)
                    ->setComponents([
                        (new ColumnHeadersRow),
                        (new FiltersRow)
                            ->addComponents([
                                (new RenderFunc(function () {
                                    return Html::style('js/daterangepicker/daterangepicker-bs3.css')
                                    . Html::script('js/moment/moment-with-locales.js')
                                    . Html::script('js/daterangepicker/daterangepicker.js')
                                    . "<style>
                                            .daterangepicker td.available.active,
                                            .daterangepicker li.active,
                                            .daterangepicker li:hover {
                                                color:black !important;
                                                font-weight: bold;
                                            }
                                       </style>";
                                }))
                                    ->setRenderSection('filters_row_column_created_date'),
                                (new DateRangePicker)
                                    ->setName('created_date')
                                    ->setRenderSection('filters_row_column_created_date')
                                    ->setDefaultValue([Carbon::now()->subDays(7)->format('Y-m-d'), Carbon::now()->format('Y-m-d')]),
                                (new RenderFunc(function () {
                                    return Html::style('js/daterangepicker/daterangepicker-bs3.css')
                                    . Html::script('js/moment/moment-with-locales.js')
                                    . Html::script('js/daterangepicker/daterangepicker.js')
                                    . "<style>
                                            .daterangepicker td.available.active,
                                            .daterangepicker li.active,
                                            .daterangepicker li:hover {
                                                color:black !important;
                                                font-weight: bold;
                                            }
                                       </style>";
                                }))
                                    ->setRenderSection('filters_row_column_date_possible'),
                                (new DateRangePicker)
                                    ->setName('date_possible')
                                    ->setRenderSection('filters_row_column_date_possible')
                                    ->setDefaultValue([Carbon::now()->subDays(7)->format('Y-m-d'), Carbon::now()->format('Y-m-d')])
                    ])
                        ,
                        (new OneCellRow)
                            ->setRenderSection(RenderableRegistry::SECTION_END)
                            ->setComponents([
                                new RecordsPerPage,
                                new ColumnsHider,
                                (new CsvExport)
                                    ->setFileName('quotations_new' . date('Y-m-d'))
                                ,
                                new ExcelExport(),
                                new PDFExport(),
                                (new HtmlTag)
                                    ->setContent('<span class="glyphicon glyphicon-refresh"></span> Filter')
                                    ->setTagName('button')
                                    ->setRenderSection(RenderableRegistry::SECTION_END)
                                    ->setAttributes([
                                        'class' => 'btn btn-success btn-sm'
                                    ])
                            ])
                    ])
                ,
                (new TFoot)
                    ->setComponents([
                        (new TotalsRow(['total_sales_ex_vat', 'total_margin_cc'])),
                        (new TotalsRow(['total_sales_ex_vat', 'total_margin_cc']))
                            ->setFieldOperations([
                                'total_sales_ex_vat' => TotalsRow::OPERATION_AVG,
                                'total_margin_cc' => TotalsRow::OPERATION_AVG,
                            ])
                        ,
                        (new OneCellRow)
                            ->setComponents([
                                new Pager,
                                (new HtmlTag)
                                    ->setAttributes(['class' => 'pull-right'])
                                    ->addComponent(new ShowingRecords)
                                ,
                            ])
                    ])
                ,
            ])
    );

    $grid = $grid->render();

    return view('admin.quotation.new', compact('grid'));
}

[Nayjest]

Did you checked SQL query generated by grid? You can use \DB::getQueryLog() or see queries in Laravel debug bar (https://github.com/barryvdh/laravel-debugbar)

[XaphanBael]

I installed the Laravel debugbar as suggested, and indeed it compares as > and <. What i cant find in the docs however is where i should place your code.

$filter->setFilteringFunc(function ($value, EloquentDataProvider $dp) {
    $dp->getBuilder()->where(DB::raw('date(some_timestamp)'), '=', $value);
});

[Nayjest]

Sorry for late response. Did you found place for mentioned code? Close this issue?

[XaphanBael]

Sorry for my late response aswell, i cant really remember what i changed in the end, but i belief it was something with the main query, and not the date handling, that worked just fine the way it was.

[Nayjest]

Ok, thanks for response anyway.