In this tutorial you will learn about the Laravel Eloquent ORM Cheat Sheet and its application with practical example.
Laravel Eloquent ORM Cheat Sheet
Table Of Contents−
Laravel Select Query
|
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 |
Laravel From Clause
|
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 |
