A great usefull helper !

  • Create automatically sql where conditions (mysql and postgresql support)
  • Create list filters functionality with flexible code for your own layout
  • Create sortable functionality with flexible code for your own layout
  • Create pagination to combine with this package methods
  • Create an automatically list length combobox
Want more ? send feedback and we increase this package with more magics.

What it do ?

Filter / Sort / Paginate your tables with this light code:

$users = \App\User::autoWhere()->autoSort()->autoPaginate();

Can I use Eloquent where too ? Yes

$users = \App\User::where('age','>=',10)->autoWhere()->autoSort()->autoPaginate();

And what this autoWhere do ?

  • if url is http://localhost/usuarios?filter[age]=>20 the query will be:

    select * from users where age > 20;

  • if url is http://localhost/usuarios?filter[age]=10:20 the query will be:

    select * from users where age >= 10 AND age <= 20;

  • if url is http://localhost/usuarios?filter[age]=<>20 the query will be:

    select * from users where age <> 10;

  • if url is http://localhost/usuarios?filter[name]=Maike the query will be:

    select * from users where UPPER(name) LIKE '%MAIKE%' ;

  • if url is http://localhost/usuarios?filter[name]=Maike&columns[name]=equal the query will be:

    select * from users where name = 'Maike' ;

  • if url is http://localhost/usuarios?filter[name]=Maike&columns[name]=text_equal the query will be:

    select * from users where UPPER(name) = 'MAIKE' ;

  • if url is http://localhost/usuarios?filter[birth]=12/12/1990 the query will be:

    select * from users where birth = '1990-12-12' ;

  • if url is http://localhost/usuarios?filter[birth]=<12/12/1990 the query will be:

    select * from users where birth < '1990-12-12' ;

  • if url is http://localhost/usuarios?filter[birth]=12/12/1990| the query will be:

    select * from users where birth >= '1990-12-12' ;

  • if url is http://localhost/usuarios?filter[birth]=12/12/1990|12/12/2000 the query will be:

    select * from users where birth between '1990-12-12' AND '2000-12-12' ;

  • if url is http://localhost/usuarios?filter[bool_field]=1 the query will be:

    if url is http://localhost/usuarios?filter[bool_field]=true the query will be:

    select * from users where bool_field = true;

  • if url is http://localhost/usuarios?filter[bool_field]=0 the query will be:

    if url is http://localhost/usuarios?filter[bool_field]=false the query will be:

    select * from users where bool_field = false;

  • if url is http://localhost/usuarios?filter[birth]=1&columns[birth]=null the query will be:

    select * from users where birth IS NULL;

  • if url is http://localhost/usuarios?filter[birth]=&columns[birth]=null the query will be:

    select * from users where birth IS NOT NULL;

  • if url is http://localhost/usuarios?filter[perfil][]=1&filter[perfil][]=2 the query will be:

    select * from users where (perfil = 1 OR perfil = 2) ;


Seriosly, don't worry with type of column and sql syntax, this will automate your querys with fresh and clean code.
It's possible to set default values for any column and the type too !

This is not a substitute for Scopes, only another way to simplify the life and can be used with scopes normally

What is necessary ?

To work all modules, it's necessary the following dependencies:

  1. php Laravel 5.*
  2. css Font-awesome
  3. js css Bootstrap*
  4. js Jquery

*Bootstrap is no more necessary

Wiki ?

Soon...

Example ?

See below:

[url] http://localhost/tickets?sort=id&order=desc&filter[t.protocol]=<7


[url] http://gsa.localhost/monitoring/alerts?filter[a.status]=opened&filter[type]=geofence


  • Can I modify the inputs place ?

    Yes, the inputs can stay outside table too or anywhere else


  • Can I modify the pagination place ?

    Too, you're free to modify layout like you want

How works ?

View

Create your table with bootstrap or not, use all your logic with blade like normally, and use the blade directives of the package:

Generate a script code to filter table without form, pass in param the selector of button will trigger your filter and inputs
@autowherescript('.btn-filter', '.input-filter')
  • param[0]: button selector - default: '.btn-filter'
  • param[1]: [input, select] selector - default: '.input-filter'
Generate a script code to async the reload table when paging, pass in 1st param many selectors elements will you want replace. In 2nd param the flag to change URL with page reloaded
@autopagesasync('.panel-table') @autopagesasync(['.panel-table tbody', '.panel-table .panel-footer']) @autopagesasync(['.panel-table tbody', '.panel-table .panel-footer'], false)
  • param[0]: replace selector - required, string or array
  • param[1]: url change flag - default: true

Usage:

< head >
@autowherescript('.btn-filter', '.input-filter')
@autopagesasync('.panel-table')
< /head >
                                
Call column header title, when click sort this column will happen.
@autosort('name','Nome')
  • param[0]: column table - required, can bring sql alias
  • param[1]: text inside < a > - not required
Bring the value previously used in filter to input tag value, to not loss when reload page.
@autowherefilter('name') @autowherefilter('id', 1) @autowherefilter('id', 1, "checked")

Can pass 2nd param, thats indicates the tag is a select option. What to do? pass the html option value in 2nd param and get "selected" return if you filter match with this 2nd param.

You can overwrite "selected" return to other string, like "checked" when pass as 3rd param

Get a select tag with length of pagination, when change reload table with new length.
@autopageslength($tickets)
  • param[0]: Paginator - use an Eloquent or QueryBuilder and finish the query with paginate() or autoPaginate() to get Paginator
Get the pagination buttons.
@autopages($tickets)
  • param[0]: Paginator - use an Eloquent or QueryBuilder and finish the query with paginate() or autoPaginate() to get Paginator

Usage:



@autowherescript('.btn-filter', '.input-filter')
@autopagesasync('.panel-table')


Registros

@foreach ($tickets as $ticket) @endforeach
@autosort('t.protocol','Protocolo') @autosort('t.start','Data Abertura') @autosort('t.end','Data Fechamento') @autosort('p.name','Portfolio')
{{$ticket->protocol}} {{$ticket->start}} {{$ticket->end}} {{$ticket->portfolio}}

Controller

Let's see it:

use App\Http\Controllers\Controller;
use Auto\Facades\Auto;

class TestsController extends Controller
{

    // without joins

    public function autowhere(){
        $tickets = \App\Ticket::autoWhere()->autoSort()->autoPaginate();
        return view("tests.ticket",compact("tickets"));
    }


    // with joins

    public function autowherealias()
    {
        $tickets = \App\Ticket::from('ticket as t')
            ->select('t.id as id',
                't.protocol as protocol',
                't.start as start',
                't.end as end',
                'p.name as portfolio'
            )
            ->leftJoin('portfolio as p', 't.portfolio_id', '=', 'p.id')
            ->autoWhere()
            ->autoSort()
            ->autoPaginate();
        return view("tests.ticket_alias", compact("tickets"));
    }


    // with overwrite column type

    public function autowhereoverwrite()
    {
        $tickets = \App\Ticket::from('ticket as t')
            ->select('t.id as id',
                't.protocol as protocol',
                't.start as start',
                't.end as end',
                'p.name as portfolio'
            )
            ->leftJoin('portfolio as p', 't.portfolio_id', '=', 'p.id')
            ->autoWhere([ 'columns' =>[ "p.name" => "equal" ] ]) // you can pass param here and overwrite the type of column to other
            ->autoSort()
            ->autoPaginate();
        return view("tests.ticket_alias", compact("tickets"));
    }


    // with 'or' conditions - Ex. url: localhost/tickets?filter[t.start]=21/05/2017&filter[t.end]=21/05/2017

    public function autowhereor()
    {
        $tickets = \App\Ticket::from('ticket as t')
            ->select('t.id as id',
                't.protocol as protocol',
                't.start as start',
                't.end as end',
                'p.name as portfolio'
            )
            ->leftJoin('portfolio as p', 't.portfolio_id', '=', 'p.id')
            ->autoWhere([ 'or' =>[ "t.start", "t.end" ] ]) // you can pass param here and create 'or' conditions with columns you wish
            ->autoSort()
            ->autoPaginate();
        // generate where: ' where ( t.start = 2017-05-21 OR t.end = 2017-05-21 ) '
        return view("tests.ticket_alias", compact("tickets"));
    }


    // set default sort params if not has in url order,sort params
    // this overwrite param setted in config file

    public function autowheresort()
    {
        $tickets = \App\Ticket::autoWhere()->autoSort( [ "id", "desc" ] )->autoPaginate(); // set columns and 'asc' or 'desc'
        return view("tests.ticket",compact("tickets"));
    }


    // set default pagination length if not has in url length params
    // this overwrite param setted in config file

    public function autowherepaginate()
    {
        $tickets = \App\Ticket::autoWhere()->autoSort()->autoPaginate( 10 ); // set overwrite default pagination
        return view("tests.ticket",compact("tickets"));
    }


    // set default value to query column (will overwrite if pass column from url param, use setField to avoid)

    public function autowheredefaultvalue()
    {
        Auto::setDefaultField( 't.start', date("Y-m-d") );

        $tickets = \App\Ticket::autoWhere()->autoSort()->autoPaginate(); // automatically get new field defaut value in Request filter param
        return view("tests.ticket",compact("tickets"));
    }
    // or
    public function autowheredefaultvaluechained()
    {
        $tickets = \App\Ticket::autoSetDefaultField( 't.start', date("Y-m-d"), "date" )->autoWhere()->autoSort()->autoPaginate();
            // autoSetDefaultField(
            //     $column,
            //     $value,
            //     $type //optional
            // )
        return view("tests.ticket",compact("tickets"));
    }

    // set value of some column and type (never change until you use this method again)

    public function autowheresetvalue()
    {
        Auto::setField( 't.end', true );
        Auto::setColumn( 't.end', 'null' );

        $tickets = \App\Ticket::autoWhere()->autoSort()->autoPaginate();
        // this will force t.end querying like null type:
        // select * from tickets where t.end is null
        return view("tests.ticket",compact("tickets"));
    }
    // or
    public function autowheresetvaluechained()
    {
        $tickets = \App\Ticket::autoSetField( 't.end', true )->autoSetColumn( 't.end', 'null' );
                ->autoWhere()->autoSort()->autoPaginate();
        // or
        $tickets = \App\Ticket::autoSetField( 't.end', true, 'null' );
                ->autoWhere()->autoSort()->autoPaginate();

        return view("tests.ticket",compact("tickets"));
    }

    // Using Soft Delete

    public function autowheresoftdelete()
    {
        Auto::withTrashed();
        // Auto::withoutTrashed();
        // Auto::onlyTrashed();
        // This method not overwrite http get param, if you pass url?trashed=2 the onlyTrashed will overwrite all methods !
        // http get param Possibilities: trashed=0 (withoutTrashed), trashed=1 (withTrashed), trashed=2 (onlyTrashed)

        $tickets = \App\Ticket::autoWhere()->autoSort()->autoPaginate();
        return view("tests.ticket",compact("tickets"));
    }

    // Using Having clause method
    // [note]: This example will active the having if url has filter[difference] like this url:
    // localhost/tickets?filter[difference]=86400 (one day example)
    // localhost/tickets?filter[difference]=>86400 (greater than one day example)

    public function autowherehaving()
    {
        Auto::having("difference","int");
        // Auto::having(["key"=>"type","key2"=>"type2"]);
        $tickets = \App\Ticket::select("ticket.*")->addSelect(DB::raw("TIMESTAMPDIFF(SECOND, ticket.start, ticket.end) as difference"))
                ->autoWhere()->autoSort()->autoPaginate();
        return view("tests.ticket",compact("tickets"));
    }
    // 'TIMESTAMPDIFF' is for mysql, while using postgresql use extract(epoch from end - start) to get seconds
}
                        

Ok, where autoWhere, autoSort and autoPaginate come from ? See Model section to understand.

It's very simple and light for your code, even I don't believe.


Model

What happens here is simple too:

use Auto\AutoWhere;
use Auto\AutoSort;
use Auto\AutoPaginate;

class Ticket extends Model
{
    use AutoWhere, AutoSort, AutoPaginate;
    ...
}
                        

Just using traits, We manipulate the QueryBuilder until we get the expected result. Of course, with other magics.

How to install ?

Now the times arrived. check out:

  1. Download package with Composer $ composer require maikealame/laravel-auto

    Check the [composer.json]

    "require": {
    ...
    "maikealame/laravel-auto": "*"
    }
                                    
  2. Add this package to your application service providers in config/app.php
    'providers' => [
    ...
    App\Providers\RouteServiceProvider::class,
    
    /*
    * Third Party Service Providers...
    */
    Auto\AutoServiceProvider::class,
    ],
                                    
  3. Publish the package configuration file to your application.
  4. $ php artisan vendor:publish --provider="Auto\AutoServiceProvider" --tag="config" See file in config/laravelauto.php

Now we done, take a coffe and start code.

Any feedback it's welcome, issues are here for it.