Eloquent cheatsheet

Posted 10 years ago - updated 9 years ago

Creation

  • Create a table: class Shop extends Eloquent {}
  • Custom table: protected $table = 'my_shops';
  • Custom primary key (instead of "id"): protected $primaryKey = 'my_key';
  • Disable "created_at" and "updated_at" columns: public $timestamps = false;
  • Fillable fields $fillable = array('title', 'author'); allows you to ` Book::create(['title'=>'Harry Potter','author'=>'J.J Rowling']);

Retrieving items

  • Retrieving all records(rows) of a given model: $shops = Shop::all();
  • Find and get a record by primary key: $shop = Shop::find(1);
  • Retrieving single record that matches a certain column : $shop = Shop::where('name', 'Starbucks')->first();
  • Retrieving specific columns of the result (by default get() returns all columns): $shops = Shop::where('name', 'Starbucks')->get('name','address','url');
  • Retrieving single column of result : $shopAddresses = Shop::where('name', 'Starbucks')->pluck('address');

Retrieving records matching a criteria:

$profitableShops = Shop::where('orders_cache','>','100')->get(); where() takes 3 parameters, name of the column, operator and value to be compared against. The operator can be one of the following: '=', '<', '>', '<=', '>=', '<>', '!=', 'like', 'not like', 'between', 'ilike'

Retrieve only a specified number of records matching a criteria:

$californianShops = Shop::where('state', 'CA')->take(10)->get();

Skip a specified number of records:

$someShops = Shops::where('name', 'Starbucks')->skip(10)->get();

Combining "skip" and "take" (useful for making custom paginators):

$shops = Shops::where('name', 'Starbucks')->skip(25)->take(25)->get();

Using forPage() to accomplish the above statement:

$shops = Shops::where('name', 'Starbucks')->forPage(2,25)->get();

Aggregates

  • Row count: $countStarbucks = Shop::where('name','Starbucks')->count();
  • Max value: $maxCoffeePrice = Product::where('name','Coffee')->max('price');
  • Min value: $minCoffeePrice = Product::where('name','Coffee')->min('price');
  • Average value: $averageCoffeePrice = Product::where('name','Coffee')->avg('price');
  • Column sum: $totalWeight = Product::where('name','Coffee')->sum('weight');
  • Increment column value: Shop::where('orders_cache','>','100')->increment('orders_cache');
  • Decrement: Shop::where('orders_cache','>','100')->decrement('orders_cache');

Get Last Insert Id

DB::getPdo()->lastInsertId();

Todo :

  • or, between, whereNull, whereIn
  • inserts
  • updates
  • deletes
  • soft deletes
  • unions
  • raw expressions
  • grouping
  • joins
  • Fillable
  • Guarded