Laravel Eloquent ORM Cheat Sheet. This tutorial provide you all the laravel eloquent ORM.

Laravel Eloquent ORM Cheat Sheet

Let’s see the eloquent methods in laravel; as follows:

Select

  ->select('col1','col2')
  ->select(array('col1','col2'))
  ->select(DB::raw('businesses.*, COUNT(reviews.id) as no_of_ratings, IFNULL(sum(reviews.score),0) as rating'))
  ->addSelect('col3','col4')
  ->distinct() // distinct select

From

  ->from('table')
  ->from(DB::raw('table, (select @n :=0) dummy'))
  ->from(DB::raw("({$subQuery->toSql()}) T ")->mergeBindings($subQuery->getQuery())

Where

$detail = Detail::where("id","!=",50)->get();
// Any of the following may be used as the second parameter (and use the third param for the value)
// =, <, >, <=, >=, <>, !=, LIKE, NOT LIKE, BETWEEN, ILIKE

$detail = Detail::where(function ($query) {
  $query->where('a', '=', 1)
      ->orWhere('b', '=', 1);
})->get();

$detail = Detail::whereRaw('age > ? and votes = 100', array(25))->get();

$detail = Detail::whereRaw(DB::raw("id in (select detail_id from students GROUP BY students.detail_id)"))->get();

$detail = Detail::whereExists(function($query){
  $query->select(DB::raw(1))
      ->from('students')
      ->whereRaw('students.detail_id = details.id')
      ->groupBy('students.detail_id')
      ->havingRaw("COUNT(*) > 0");
})->get();
// Any of the following may be used instead of Detail::whereExists
// ->orWhereExists(), ->whereNotExists(), ->orWhereNotExists()

$detail = Detail::whereIn('column',[1,2,3])->get();
// Any of the following may be used instead of Detail::whereExists
// ->orWhereIn(),

$detail = Detail::whereNotIn('id', function($query){
  $query->select('student_id')
  ->from('students')
  ->groupBy('students.student_id');
})->get();

// Any of the following may be used instead of Detail::whereExists
// ->whereNotIn(), ->orWhereNotIn

Joins

$product = Product:where('id', $productId)
    ->join('businesses','product.business_id','=','businesses.id')
    ->select('product.id','businesses.name')->first();

$product = Product:where('id', $productId)
    ->leftJoin('businesses','product.business_id', '=', 'businesses.id')
    ->select('product.id','businesses.name')->first();

$product = Product:where('id', $productId)
    ->join('businesses',function($join) use($cats) {
      $join->on('product.business_id', '=', 'businesses.id')
    ->on('product.id', '=', $cats, 'and', true);})->first();

Eager Loading

  ->with('table1','table2')
  ->with(array('table1','table2','table1.nestedtable3'))
  ->with(array('posts' => function($query) use($name){
    $query->where('title', 'like', '%'.$name.'%')
      ->orderBy('created_at', 'desc');
  }))

Grouping

  ->groupBy('state_id','locality')
  ->havingRaw('count > 1 ')
  ->having('items.name','LIKE',"%$keyword%")
  ->orHavingRaw('brand LIKE ?',array("%$keyword%"))

Cache

  ->remember($minutes)
  ->rememberForever()

Offset & Limit

  ->take(10)
  ->limit(10)
  ->skip(10)
  ->offset(10)
  ->forPage($pageNo, $perPage)

Order

  ->orderBy('id','DESC')
  ->orderBy(DB::raw('RAND()'))
  ->orderByRaw('type = ? , type = ? ', array('published','draft'))
  ->latest() // on 'created_at' column
  ->latest('column')
  ->oldest() // on 'created_at' column
  ->oldest('column')

Create

  ->insert(array('email' => '[email protected]', 'votes' => 0))
  ->insert(array(
    array('email' => '[email protected]', 'votes' => 0),
    array('email' => '[email protected]', 'votes' => 0)
  )) //batch insert
  ->insertGetId(array('email' => '[email protected]', 'votes' => 0)) //insert and return id

Update

  ->update(array('email' => '[email protected]'))
  ->update(array('column' => DB::raw('NULL')))
  ->increment('column')
  ->decrement('column')
  ->touch() //update timestamp

Delete

  ->delete()
  ->forceDelete() // when softdeletes enabled
  ->destroy($ids) // delete by array of primary keys
  ->roles()->detach() //delete from pivot table: associated by 'belongsToMany'

Getters

  ->find($id)
  ->find($id, array('col1','col2'))
  ->findOrFail($id)
  ->findMany($ids, $columns)
  ->first(array('col1','col2'))
  ->firstOrFail()
  ->all()
  ->get()
  ->get(array('col1','col2'))
  ->getFresh() // no caching
  ->getCached() // get cached result
  ->chunk(1000, function($rows){
    $rows->each(function($row){

    });
  })
  ->lists('column') // numeric index
  ->lists('column','id') // 'id' column as index
  ->lists('column')->implode('column', ',') // comma separated values of a column
  ->pluck('column')  //Pluck a single column's value from the first result of a query.
  ->value('column')  //Get a single column's value from the first result of a query.

Paginated results

  ->paginate(10)
  ->paginate(10, array('col1','col2'))
  ->simplePaginate(10)
  ->getPaginationCount() //get total no of records

Aggregate

  ->count()
  ->count('column')
  ->count(DB::raw('distinct column'))
  ->max('rating')
  ->min('rating')
  ->sum('rating')
  ->avg('rating')
  ->aggregate('sum', array('rating')) // use of aggregate functions

Others

  ->toSql() // output sql query
  ->exists() // check if any row exists
  ->fresh() // Return a fresh data for current model from database

Object methods

  ->toArray() //
  ->toJson()
  ->relationsToArray() //Get the model's relationships in array form.
  ->implode('column', ',') // comma separated values of a column
  ->isDirty()
  ->getDirty() //Get the attributes that have been changed but not saved to DB

Debugging

DB::enableQueryLog();
DB::getQueryLog();
Model::where()->toSql() // output sql query

Recommended laravel tutorial