Laravel Eloquent Cheat Sheet Eloquent ORM

 Laravel Eloquent ORM Cheat Sheet. This tutorial provide you all the laravel 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 36 37 38 39 40 41 42

->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') //where `column` is null ->orWhereNull('column') //or where `column` is null ->whereNotNull('column')  //where `column` is not null ->orWhereNotNull('column')  //or where `column` is not null   ->whereDay() ->whereMonth('column', '=', 1) // ->whereYear('column', '>', 2000) //uses sql YEAR() function on 'column' ->whereDate('column', '>', '2000-01-01')

Joins

1 2 3 4 5 6 7 8 9

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

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' => 'john@example.com', 'votes' => 0)) ->insert(array(     array('email' => 'taylor@example.com', 'votes' => 0),   array('email' => 'dayle@example.com', 'votes' => 0) )) //batch insert ->insertGetId(array('email' => 'john@example.com', 'votes' => 0)) //insert and return id

Update

1 2 3 4 5

->update(array('email' => 'john@example.com')) ->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