Database

DB class namespace

Illuminate\Support\Facades\DB

DB::connection('connection_name');
DB::statement('drop table users');
DB::listen(function($sql, $bindings, $time){ code_here; });
DB::transaction(function(){ transaction_code_here; });
// Cache a query for $time minutes
DB::table('users')->remember($time)->get();
// Escape raw input
DB::raw('sql expression here');

Raw Expressions

// return rows
DB::select('select * from users where id = ?', array('value'));
// return nr affected rows
DB::insert('insert into foo set bar=2');
DB::update('update foo set bar=2');
DB::delete('delete from bar');
// returns void
DB::statement('update foo set bar=2');

Selects

DB::table('name')->get(); // get all rows
DB::table('name')->distinct()->get();
DB::table('posts')->select('body as post_content')->get(); // set column alias
DB::table('name')->where('name', '=', 'John')->get(); // find data by logic
DB::table('name')->whereBetween('column', array(1, 100))->get();
DB::table('name')->orWhereBetween('column', array(200, 300))->get();
DB::table('name')->whereIn('column', array(1, 2, 3))->get();
DB::table('name')->whereNotIn('column', array(1, 2, 3))->get();
DB::table('name')->whereNull('column')->get();
DB::table('name')->whereNotNull('column')->get();
DB::table('name')->groupBy('column')->get();

// Default Eloquent sort is ascendant
DB::table('name')->orderBy('column')->get();
DB::table('name')->orderBy('column','desc')->get();
DB::table('name')->having('count', '>', 100)->get();
DB::table('name')->skip(10)->take(5)->get();
DB::table('name')->first();
DB::table('name')->pluck('column');
DB::table('name')->lists('column');
// Joins
DB::table('name')->join('table', 'name.id', '=', 'table.id')
    ->select('name.id', 'table.email');

Inserts, Updates, Deletes

// Insert single data row
DB::table('name')->insert( ['name' => 'John', 'email' => '[email protected]'] );

// Inserting data and return current row id
DB::table('posts')->insertGetId( ['title' => 'Post Title', 'body' => 'lorem ipsum ...'] );

// Insert multiple rows data
DB::table('posts')->insert( [
    [ 'title' => 'Title 1', 'body' => 'Post content 1' ],
    [ 'title' => 'Title 2', 'body' => 'Post content 2' ],
    [ 'title' => 'Title 3', 'body' => 'Post content 3' ],
    [ 'title' => 'Title 4', 'body' => 'Post content 4' ],
    [ 'title' => 'Title 5', 'body' => 'Post content 5' ]
]);

// Update an entry
DB::table('posts')
    ->where(.. //logic ..)
    ->update( [ 'title' => 'title updated' , 'body' => 'body updated' ] );

// Delete everything from a table
DB::table('name')->delete();
DB::table('name')->truncate();

// Delete specific records
DB::table('name')->where(.. //logic ..)->delete();

Calculation actions

DB::table('name')->count();
DB::table('name')->max('column');
DB::table('name')->min('column');
DB::table('name')->avg('column');
DB::table('name')->sum('column');
DB::table('name')->increment('column');
DB::table('name')->increment('column', $amount);
DB::table('name')->decrement('column');
DB::table('name')->decrement('column', $amount);
DB::table('name')->remember(5)->get();
DB::table('name')->remember(5, 'cache-key-name')->get();
DB::table('name')->cacheTags('my-key')->remember(5)->get();
DB::table('name')->cacheTags(array('my-first-key','my-second-key'))->remember(5)->get();