PHP
laravel
Eloquent

Laravel Eloquent Cheat Sheet

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()

From

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

Where

->where('column','value')
->where('column','LIKE','%'.$value.'%')
->where(function ($query) {
  $query->where('a', '=', 1)
      ->orWhere('b', '=', 1);
})
->orWhere('column','!=', 'value')
->whereRaw('age > ? and votes = 100', array(25))

->whereRaw(DB::raw("id in (select city_id from addresses GROUP BY addresses.city_id)"))

->whereExists(function($query){
  $query->select(DB::raw(1))
      ->from('business_language')
      ->whereRaw('business_language.language_id = languages.id')
      ->groupBy('business_language.language_id')
      ->havingRaw("COUNT(*) > 0");
})
->orWhereExists()
->whereNotExists()
->orWhereNotExists()

->whereIn('column',[1,2,3])
->orWhereIn()
->whereNotIn('id', function($query){
  $query->select('city_id')
  ->from('addresses')
  ->groupBy('addresses.city_id');
})
->whereNotIn()
->orWhereNotIn

->whereNull('column')
->orWhereNull('column')
->whereNotNull('column')
->orWhereNotNull('column')

->whereDay()
->whereMonth('column', '=', 1)
->whereYear('column', '>', 2000)
->whereDate('column', '>', '2000-01-01')

Joins

->join('business_category','business_category.business_id','=','businesses.id')
->leftJoin('reviews','reviews.business_id', '=', 'businesses.id')
->join('business_category',function($join) use($cats) {
  $join->on('business_category.business_id', '=', 'businesses.id')
  ->on('business_category.id', '=', $cats, 'and', true);
})
->join(DB::raw('(SELECT *, ROUND(AVG(rating),2) avg FROM reviews WHERE rating!=0 GROUP BY item_id ) T' ), function($join){
  $join->on('genre_relation.movie_id', '=', 'T.id')
})

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()
->latest('column')
->oldest()
->oldest('column')

Create

->insert(array('email' => 'john@example.com', 'votes' => 0))
->insert(array(   
  array('email' => 'taylor@example.com', 'votes' => 0),
  array('email' => 'dayle@example.com', 'votes' => 0)
))
->insertGetId(array('email' => 'john@example.com', 'votes' => 0))

Update

->update(array('email' => 'john@example.com'))
->update(array('column' => DB::raw('NULL')))
->increment('column')
->decrement('column')
->touch()

Delete

->delete()
->forceDelete()
->destroy($ids)
->roles()->detach()

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()
->getCached()
->chunk(1000, function($rows){
  $rows->each(function($row){

  });
})
->lists('column')
->lists('column','id')
->lists('column')->implode('column', ',')
->pluck('column') 
->value('column') 

Paginated results

->paginate(10)
->paginate(10, array('col1','col2'))
->simplePaginate(10)
->getPaginationCount()

Aggregate

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

Others

->toSql()
->exists()
->fresh()

Object methods

->toArray()
->toJson()
->relationsToArray()
->implode('column', ',')
->isDirty()
->getDirty()

Debugging

DB::enableQueryLog();
DB::getQueryLog();
Model::where()->toSql();