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 |