• 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

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

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

Soon...

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

View

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

< head >
@autowherescript('.btn-filter', '.input-filter')
@autopagesasync('.panel-table')
< /head >
                                

@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')
                

            
            

                

                

                

                

                    
                        Portfolio 1
                        Portfolio 2
                        Portfolio 3
                    
                
                

            
            
            

                    
{{$ticket->protocol}}
                    
{{$ticket->start}}
                    
{{$ticket->end}}
                    
{{$ticket->portfolio}}
                    

                    
                
        
    
    

        

            Mostrando @autopageslength($tickets) de {{$tickets->total()}}
        
        

            @autopages($tickets)
        
    


                                

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.

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.
$ php artisan vendor:publish --provider="Auto\AutoServiceProvider" --tag="config" See file in config/laravelauto.php