Skip to content

Instantly share code, notes, and snippets.

@hassansin
Last active April 10, 2025 08:32

Revisions

  1. hassansin revised this gist May 13, 2016. 1 changed file with 3 additions and 1 deletion.
    4 changes: 3 additions & 1 deletion eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -121,7 +121,7 @@
    /*Delete*/
    ->delete()
    ->forceDelete() // when softdeletes enabled
    ->destroy($ids) // delete by primary key
    ->destroy($ids) // delete by array of primary keys
    ->roles()->detach() //delete from pivot table: associated by 'belongsToMany'


    @@ -147,6 +147,7 @@
    ->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*/
    ->paginate(10)
    ->paginate(10, array('col1','col2'))
    @@ -166,6 +167,7 @@
    /*Others*/
    ->toSql() // output sql query
    ->exists() // check if any row exists
    ->fresh() // Return a fresh data for current model from database

    /*Object methods*/
    ->toArray() //
  2. hassansin revised this gist Mar 21, 2016. 1 changed file with 6 additions and 1 deletion.
    7 changes: 6 additions & 1 deletion eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -173,4 +173,9 @@
    ->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
    ->getDirty() //Get the attributes that have been changed but not saved to DB

    //Debugging
    DB::enableQueryLog();
    DB::getQueryLog();
    Model::where()->toSql() // output sql query
  3. hassansin revised this gist Mar 21, 2016. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -116,6 +116,7 @@
    ->update(array('column' => DB::raw('NULL')))
    ->increment('column')
    ->decrement('column')
    ->touch() //update timestamp

    /*Delete*/
    ->delete()
  4. Hassansin revised this gist Nov 21, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -145,7 +145,7 @@
    ->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*/
    ->paginate(10)
    ->paginate(10, array('col1','col2'))
  5. Hassansin revised this gist Nov 11, 2015. No changes.
  6. Hassansin revised this gist Nov 11, 2015. No changes.
  7. Hassansin revised this gist Nov 10, 2015. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -16,6 +16,10 @@
    /*Query*/
    ->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))

  8. Hassansin revised this gist Jul 27, 2015. 1 changed file with 5 additions and 0 deletions.
    5 changes: 5 additions & 0 deletions eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -6,7 +6,11 @@
    ->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*/
    ->from('table')
    ->from(DB::raw('table, (select @n :=0) dummy'))
    ->from(DB::raw("({$subQuery->toSql()}) T ")->mergeBindings($subQuery->getQuery())


    /*Query*/
    @@ -113,6 +117,7 @@
    ->delete()
    ->forceDelete() // when softdeletes enabled
    ->destroy($ids) // delete by primary key
    ->roles()->detach() //delete from pivot table: associated by 'belongsToMany'


    /*Getters*/
  9. Hassansin revised this gist Jun 27, 2015. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -105,6 +105,7 @@

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

  10. Hassansin revised this gist Jun 26, 2015. 1 changed file with 17 additions and 2 deletions.
    19 changes: 17 additions & 2 deletions eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -108,10 +108,17 @@
    ->increment('column')
    ->decrement('column')

    /*Delete*/
    ->delete()
    ->forceDelete() // when softdeletes enabled
    ->destroy($ids) // delete by primary key


    /*Getters*/
    ->find($id)
    ->find($id, array('col1','col2'))
    ->findOrFail($id)
    ->findMany($ids, $columns)
    ->first(array('col1','col2'))
    ->firstOrFail()
    ->all()
    @@ -143,8 +150,16 @@
    ->min('rating')
    ->sum('rating')
    ->avg('rating')
    ->aggregate('sum', array('rating')) // use of aggregate function
    ->aggregate('sum', array('rating')) // use of aggregate functions

    /*Others*/
    ->toSql() // output sql query
    ->exists() // check if any row exists
    ->exists() // check if any row exists

    /*Object methods*/
    ->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
  11. Hassansin revised this gist Jun 26, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -47,7 +47,7 @@
    ->whereDay()
    ->whereMonth('column', '=', 1) //
    ->whereYear('column', '>', 2000) //uses sql YEAR() function on 'column'
    ->whereDate('column', '>', '2000-01-01)
    ->whereDate('column', '>', '2000-01-01')

    /*Joins*/
    ->join('business_category','business_category.business_id','=','businesses.id')
  12. Hassansin revised this gist Jun 26, 2015. 1 changed file with 25 additions and 6 deletions.
    31 changes: 25 additions & 6 deletions eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -13,14 +13,10 @@
    ->where('column','value')
    ->where('column','LIKE','%'.$value.'%')
    ->orWhere('column','!=', 'value')
    ->whereIn('column',[1,2,3])
    ->whereRaw('age > ? and votes = 100', array(25))
    ->whereNotIn('id', function($query){
    $query->select('city_id')
    ->from('addresses')
    ->groupBy('addresses.city_id');
    })

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

    ->whereExists(function($query)
    {
    $query->select(DB::raw(1))
    @@ -29,6 +25,29 @@
    ->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*/
    ->join('business_category','business_category.business_id','=','businesses.id')
  13. Hassansin revised this gist Jun 26, 2015. 1 changed file with 10 additions and 2 deletions.
    12 changes: 10 additions & 2 deletions eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -65,9 +65,12 @@
    ->limit(10)
    ->skip(10)
    ->offset(10)
    ->forPage($pageNo, $perPage)

    /*Order*/
    ->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
    @@ -104,10 +107,14 @@
    })
    ->lists('column') // numeric index
    ->lists('column','id') // 'id' column as index
    ->lists('column')->implode('column', ',') // comma separated values
    ->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.

    /*Paginated results*/
    ->paginate(10)
    ->paginate(10, array('col1','col2'))
    ->simplePaginate(10)
    ->getPaginationCount() //get total no of records

    /*Aggregate*/
    ->count()
    @@ -120,4 +127,5 @@
    ->aggregate('sum', array('rating')) // use of aggregate function

    /*Others*/
    ->toSql() // output sql query
    ->toSql() // output sql query
    ->exists() // check if any row exists
  14. Hassansin revised this gist Jun 26, 2015. 1 changed file with 24 additions and 1 deletion.
    25 changes: 24 additions & 1 deletion eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -73,6 +73,18 @@
    ->oldest() // on 'created_at' column
    ->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)
    )) //batch insert
    ->insertGetId(array('email' => 'john@example.com', 'votes' => 0)) //insert and return id

    /*Update*/
    ->update(array('email' => 'john@example.com'))
    ->increment('column')
    ->decrement('column')

    /*Getters*/
    ->find($id)
    @@ -96,5 +108,16 @@
    ->paginate(10)
    ->paginate(10, array('col1','col2'))
    ->simplePaginate(10)

    /*Aggregate*/
    ->count()
    ->count(DB::raw('distinct businesses.id'));
    ->count('column')
    ->count(DB::raw('distinct column'))
    ->max('rating')
    ->min('rating')
    ->sum('rating')
    ->avg('rating')
    ->aggregate('sum', array('rating')) // use of aggregate function

    /*Others*/
    ->toSql() // output sql query
  15. Hassansin revised this gist Jun 26, 2015. 1 changed file with 32 additions and 6 deletions.
    38 changes: 32 additions & 6 deletions eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,13 @@
    Model::

    /*Select*/
    ->select('col1','col2')
    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('table')


    /*Query*/
    ->where('column','value')
    @@ -31,7 +37,7 @@
    $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_movie WHERE rating!=0 GROUP BY item_id ) T' ), function($join){
    ->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')
    })

    @@ -54,21 +60,41 @@
    ->remember($minutes)
    ->rememberForever()

    /*Offset & Limit & Order*/
    /*Offset & Limit*/
    ->take(10)
    ->limit(10)
    ->skip(10)
    ->offset(10)

    /*Order*/
    ->orderBy('id','DESC')
    ->latest() // on 'created_at' column
    ->latest('column')
    ->oldest() // on 'created_at' column
    ->oldest('column')


    /*Getters*/
    ->find($id)
    ->find($id, array('col1','col2'))
    ->findOrFail($id)
    ->first()
    ->first(array('col1','col2'))
    ->firstOrFail()
    ->all()
    ->get()
    ->lists('id','name')
    ->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
    ->paginate(10)
    ->paginate(10, array('col1','col2'))
    ->simplePaginate(10)
    ->count()
    ->count(DB::raw('distinct businesses.id'));
  16. Hassansin revised this gist Jun 24, 2015. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -29,10 +29,10 @@
    ->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','=',DB::raw("$cats");
    ->on('business_category.id', '=', $cats, 'and', true);
    })
    ->join(DB::raw('(SELECT *, ROUND(AVG(rating),2) avg FROM reviews_movie WHERE rating!=0 GROUP BY item_id ) T' ), function($join){
    $join->on('genre_relation.movie_id', '=', 'T.id')
    $join->on('genre_relation.movie_id', '=', 'T.id')
    })

    /*Eager Loading */
  17. Hassansin revised this gist Jun 23, 2015. 1 changed file with 22 additions and 7 deletions.
    29 changes: 22 additions & 7 deletions eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -2,6 +2,7 @@
    /*Select*/
    ->select('col1','col2')
    ->select(DB::raw('businesses.*, COUNT(reviews.id) as no_of_ratings, IFNULL(sum(reviews.score),0) as rating'))

    /*Query*/
    ->where('column','value')
    ->where('column','LIKE','%'.$value.'%')
    @@ -14,15 +15,26 @@
    ->groupBy('addresses.city_id');
    })
    ->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");
    })

    /*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');
    $join->on('business_category.business_id', '=', 'businesses.id')
    ->on('business_category.id','=',DB::raw("$cats");
    })
    ->leftJoin('reviews',function($join) {
    $join = $join->on('reviews.business_id', '=', 'businesses.id');
    ->join(DB::raw('(SELECT *, ROUND(AVG(rating),2) avg FROM reviews_movie 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'))
    @@ -34,7 +46,9 @@

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

    /*Cache*/
    ->remember($minutes)
    @@ -53,7 +67,8 @@
    ->firstOrFail()
    ->all()
    ->get()
    ->lists()
    ->paginate()
    ->lists('id','name')
    ->paginate(10)
    ->simplePaginate(10)
    ->count()
    ->count(DB::raw('distinct businesses.id'));
  18. Hassansin created this gist Jun 23, 2015.
    59 changes: 59 additions & 0 deletions eloquent-cheatsheet.php
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,59 @@
    Model::
    /*Select*/
    ->select('col1','col2')
    ->select(DB::raw('businesses.*, COUNT(reviews.id) as no_of_ratings, IFNULL(sum(reviews.score),0) as rating'))
    /*Query*/
    ->where('column','value')
    ->where('column','LIKE','%'.$value.'%')
    ->orWhere('column','!=', 'value')
    ->whereIn('column',[1,2,3])
    ->whereRaw('age > ? and votes = 100', array(25))
    ->whereNotIn('id', function($query){
    $query->select('city_id')
    ->from('addresses')
    ->groupBy('addresses.city_id');
    })
    ->whereRaw(DB::raw("id in (select city_id from addresses GROUP BY addresses.city_id)"))

    /*Joins*/
    ->join('business_category',function($join) use($cats) {
    $join->on('business_category.business_id', '=', 'businesses.id');
    })
    ->leftJoin('reviews',function($join) {
    $join = $join->on('reviews.business_id', '=', 'businesses.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 ')

    /*Cache*/
    ->remember($minutes)
    ->rememberForever()

    /*Offset & Limit & Order*/
    ->take(10)
    ->limit(10)
    ->skip(10)
    ->orderBy('id','DESC')

    /*Getters*/
    ->find($id)
    ->findOrFail($id)
    ->first()
    ->firstOrFail()
    ->all()
    ->get()
    ->lists()
    ->paginate()
    ->count()
    ->count(DB::raw('distinct businesses.id'));