Crud: How can I make custom filters?

[leomongeg]

Hi folks,

how can I make custom filters to apply in the list view for advance filtering. For example a date range filter.

Thanks in advance.
Greetings.
Leonardo Monge García.

[tabacitu]

Hi @leomongeg ,

This isn't very well documented, but the functionality is there. You can use the addClause methods for that.

// ------ ADVANCED QUERIES
$this->crud->addClause('active');
$this->crud->addClause('type', 'car');
$this->crud->addClause('where', 'name', '==', 'car');
$this->crud->addClause('whereName', 'car');
$this->crud->addClause('whereHas', 'posts', function($query) {
     $query->activePosts();
 });
$this->crud->orderBy();
$this->crud->groupBy();
$this->crud->limit();

Cheers!

[tabacitu]

Sorry for being so dismissive earlier. It's been a tough week. Does this fix you issue? Ok to leave it closed?

[Frasaccordi]

hai im looking some answer for similiar question. i try to query with join statment

what i want is join 2 table ."questions"(current controller set model) and "package_has_questions" table that set as pivot and only contain 2 foreign key id coloumn: Package_id and question_id;

i want to show all question from question table that registered in pivot table where package_id=1.
but i not get it even for join statment.

what i did is:

$this->crud->addClause('join','package_has_questions', 'questions.id', '=','package_has_questions.question_id' );

its return eror

SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'on clause' (SQL: select * from questions inner join package_has_questions on questions.id = ``)

is it bug or i use wrong way? its look like 'package_has_questions.question_id' is not in the sql statment

[tabacitu]

Hi @Frasaccordi ,

There's no way to debug that myself, but you can take a look at what happens with dd($this->crud->query);. I've never done joins on the query myself.

But you can totally customize the query with $this->crud->query = $whatever; The $query property is intentionally public, so that it's easy to modify from your controller. Just make sure it returns a collection of Question.

Sorry I can't be of more help. Cheers!

[Frasaccordi]

Hi @tabacitu ,

after a dozen of experiment i find something like joining with function:
$this->crud->addClause('join', 'package_has_questions', function($query) { //////join with function $packId= \Route::current()->parameter('packageId'); //param from route $query ->on('questions.id','=','package_has_questions.question_id') ->where('package_has_questions.package_id','=',$packId);

its work well for me. i use addClause 'join' with function, and inside the function, we can do some similiar quering like we do in laravel

thanks for always response @tabacitu, I Appreciate it!!

[Frasaccordi]

 $this->crud->addClause('join', 'package_has_questions', function($query) {  
                 // join with function
                 $packId= \Route::current()->parameter('packageId');
                 $query->on('questions.id', '=', 'package_has_questions.question_id')
                       ->where('package_has_questions.package_id', '=', $packId);

i hope this is looks better

[tabacitu]

Great, thanks for sharing this. Cheers!

[Frasaccordi]

Your Welcome tabacitu

[IvanBernatovic]

Hi. Filters are really nice addition. I would like to have daterange filter. Is there any built in solution for that? @tabacitu , I saw you mentioned daterange in previous comments but I can't find anything in docs.

[tabacitu]

Hi @IvanBernatovic - haven't built the daterange filter yet, but it is planned. We'll be using dangrossman's jquery plugin. Can't give you an ETA, sorry. Probably early or mid January.

Cheers!

[b8ne]

Hey @tabacitu have you started on the filter yet? I just implemented one using adminlte's range picker. Its a little crude, and some names are hard coded, but id be happy to share it with you to save time? I dont really have time to fix it up to be ready for a PR.

[tabacitu]

@b8ne , haven't done it yet, no. If you can share any code, please do :-)

Cheers!

[b8ne]

@tabacitu
date_range.blade.php

{{-- Date Range Backpack CRUD filter --}}
{{-- Example Backpack CRUD filter --}}

<li filter-name="{{ $filter->name }}"
    filter-type="{{ $filter->type }}"
    class="dropdown {{ Request::get($filter->name)?'active':'' }}">
    <a style="pointer-events: none; display: inline-block;" href="#" class="dropdown-toggle">Date Range</a>
    <input type="hidden" name="{{ $filter->name }}">
    <input type="text" name="date-fake" value="01/01/2017 - 31/12/2017" style="width: 150px;" />
</li>


{{-- ########################################### --}}
{{-- Extra CSS and JS for this particular filter --}}

{{-- FILTERS EXTRA CSS  --}}
{{-- push things in the after_styles section --}}

@push('crud_fields_styles')
@endpush


{{-- FILTERS EXTRA JS --}}
{{-- push things in the after_scripts section --}}


{{-- FILTER JAVASCRIPT CHECKLIST

- redirects to a new URL for standard DataTables
- replaces the search URL for ajax DataTables
- users have a way to clear this filter (and only this filter)
- filter:clear event on li[filter-name], which is called by the "Remove all filters" button, clears this filter;

END OF FILTER JAVSCRIPT CHECKLIST --}}

@push('crud_list_scripts')


<script>
    function findGetParameter(parameterName) {
        var result = null,
            tmp = [];
        location.search
            .substr(1)
            .split("&")
            .forEach(function (item) {
                tmp = item.split("=");
                if (tmp[0] === parameterName) result = decodeURIComponent(tmp[1]);
            });
        return result;
    }

    jQuery(document).ready(function($) {
        // Check if param exists
        let currentVal = findGetParameter('{{ $filter->name }}');
        let start = null;
        let end = null;
        if (currentVal !== null) {
            let values = currentVal.split('_');
            start = values[0];
            end = values[1];
        }

        // Attach date range picker
        $('input[name="date-fake"]').daterangepicker({
                locale: {
                    format: 'DD-MM-Y'
                },
                startDate: start != null ? start : '01-01-2017',
                endDate: end != null ? end : '21-12-2020'
            },
            function(start, end, label) {
                $('input[name={{ $filter->name }}]').value = start.format('DD-MM-YY') + "_" + end.format('DD-MM-YY');
                filterClick(start.format('DD-MM-YY') + "_" + end.format('DD-MM-YY'));
            });

        let filterClick = function(value) {
            window.location.hred = URI();
            var parameter = '{{ $filter->name }}';

            @if (!$crud->ajaxTable())
            var current_url = normalizeAmpersand("{{ Request::fullUrl() }}");
            var new_url = addOrUpdateUriParameter(current_url, parameter, value);

            // refresh the page to the new_url
            new_url = normalizeAmpersand(new_url.toString());
            window.location.href = new_url;
            @else
            // behaviour for ajax table
            var ajax_table = $("#crudTable").DataTable();
            var current_url = ajax_table.ajax.url();
            var new_url = addOrUpdateUriParameter(current_url, parameter, value);

            // replace the datatables ajax url with new_url and reload it
            new_url = normalizeAmpersand(new_url.toString());
            ajax_table.ajax.url(new_url).load();

            // mark this filter as active in the navbar-filters
            if (URI(new_url).hasQuery('{{ $filter->name }}', true)) {
                $("li[filter-name={{ $filter->name }}]").removeClass('active').addClass('active');
            }
            else
            {
                $("li[filter-name={{ $filter->name }}]").trigger("filter:clear");
            }
            @endif
        };
    });
</script>




@endpush

{{-- End of Extra CSS and JS --}}
{{-- ########################################## --}}

CrudController

$this->crud->addFilter([
            'name' => 'date',
            'type' => 'date_range',
            'label' => 'Date Range'
        ],[],
            function($value) {
                $dates = explode('_', $value);
                $start = Carbon::createFromFormat('d-m-y', $dates[0]);
                $end = Carbon::createFromFormat('d-m-y', $dates[1]);
                $this->crud->addClause('where', 'date', '>', $start);
                $this->crud->addClause('where', 'date', '<', $end);
            });

Some parts aren't the prettiest but it was quick and does the job

[CHOMNANP]

@b8ne : I have tried your solution. Yet the front end say that the datetime is not funciton.
Seem like the JS Library for this control.

Any js library that you're using for this particular solution?

Thanks.

[b8ne]

Yeah @CHOMNANP try this in base layout.blade.php
```

 @yield('after_styles')

@@ -105,10 +106,12 @@

• 
  
  
  
  
• 
  ```
  Again this was a quick fix solution. I think a PR should utilise as many current libraries as possible.

[CHOMNANP]

Awesome.
I'll check this out later.

Thanks.

[jambari]

I had this error when applying date_range filter on crud 3.2, anyone had this too ?

InvalidArgumentException in Carbon.php line 582:
Unexpected data found.
The separation symbol could not be found
Trailing data

[milanchheda]

@Frasaccordi - Thanks for showing on how we can join tables. I was able to do that, but I am unable to select column names from the joined table. I want to show all columns from both the tables, how do we get that?

[pathaksa]

When I'm doing a table join, the advertiser_bills.id is replaced by the advertisers.id
$this->crud->addClause('join', 'advertisers', function ($query) use ($value) { $query->on('advertiser_bills.advertiser_id', '=', 'advertisers.id') ->whereNotNull('advertisers.stats_request_method'); });

So now I am unable to use the firstTable.id in the CRUD columns .

Any solution to this?

EDIT:

I found solution:
https://github.com/Laravel-Backpack/CRUD/issues/1205

I just have to add this:
$this->crud->addClause('select', 'advertiser_bills.*');