Back to Home

Laravel Eloquent Cheat Sheet | Eloquent ORM

Select

1
2
3
4
5
->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

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

Where

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
$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

1
2
3
4
5
6
7
8
9
10
11
12
$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

1
2
3
4
5
6
->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

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

Cache

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

Offset & Limit

1
2
3
4
5
->take(10)
->limit(10)
->skip(10)
->offset(10)
->forPage($pageNo, $perPage)

Order

1
2
3
4
5
6
7
->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

1
2
3
4
5
6
->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

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

Delete

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

Getters

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
->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

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

Aggregate

1
2
3
4
5
6
7
8
->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

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

Object methods

1
2
3
4
5
6
->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

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

Recent Posts