Laravel Eloquent Cheat Sheet | Eloquent ORM

Laravel Eloquent Cheat Sheet | Eloquent ORM

Laravel Eloquent ORM Cheat Sheet. This tutorial provide you all the laravel eloquent ORM.

Laravel Eloquent ORM Cheat Sheet

Let’s see the eloquent methods in laravel; as follows:

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() // distinct select

From

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

Where

$detail = Detail::where("id","!=",50)->get();
// Any of the following may be used as the second parameter (and use the third param for the value)
// =, <, >, <=, >=, <>, !=, LIKE, NOT LIKE, BETWEEN, ILIKE

$detail = Detail::where(function ($query) {
  $query->where('a', '=', 1)
      ->orWhere('b', '=', 1);
})->get();

$detail = Detail::whereRaw('age > ? and votes = 100', array(25))->get();

$detail = Detail::whereRaw(DB::raw("id in (select detail_id from students GROUP BY students.detail_id)"))->get();

$detail = Detail::whereExists(function($query){
  $query->select(DB::raw(1))
      ->from('students')
      ->whereRaw('students.detail_id = details.id')
      ->groupBy('students.detail_id')
      ->havingRaw("COUNT(*) > 0");
})->get();
// Any of the following may be used instead of Detail::whereExists
// ->orWhereExists(), ->whereNotExists(), ->orWhereNotExists()

$detail = Detail::whereIn('column',[1,2,3])->get();
// Any of the following may be used instead of Detail::whereExists
// ->orWhereIn(),

$detail = Detail::whereNotIn('id', function($query){
  $query->select('student_id')
  ->from('students')
  ->groupBy('students.student_id');
})->get();

// Any of the following may be used instead of Detail::whereExists
// ->whereNotIn(), ->orWhereNotIn

Joins

$product = Product:where('id', $productId)
    ->join('businesses','product.business_id','=','businesses.id')
    ->select('product.id','businesses.name')->first();

$product = Product:where('id', $productId)
    ->leftJoin('businesses','product.business_id', '=', 'businesses.id')
    ->select('product.id','businesses.name')->first();

$product = Product:where('id', $productId)
    ->join('businesses',function($join) use($cats) {
      $join->on('product.business_id', '=', 'businesses.id')
    ->on('product.id', '=', $cats, 'and', true);})->first();

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

Create

  ->insert(array('email' => '[email protected]', 'votes' => 0))
  ->insert(array(   
    array('email' => '[email protected]', 'votes' => 0),
    array('email' => '[email protected]', 'votes' => 0)
  )) //batch insert
  ->insertGetId(array('email' => '[email protected]', 'votes' => 0)) //insert and return id

Update

  ->update(array('email' => '[email protected]'))
  ->update(array('column' => DB::raw('NULL')))
  ->increment('column')
  ->decrement('column')
  ->touch() //update timestamp

Delete

  ->delete()
  ->forceDelete() // when softdeletes enabled
  ->destroy($ids) // delete by array of primary keys
  ->roles()->detach() //delete from pivot table: associated by 'belongsToMany'

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

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

Aggregate

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

  ->toSql() // output sql query
  ->exists() // check if any row exists
  ->fresh() // Return a fresh data for current model from database

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

Debugging

DB::enableQueryLog();
DB::getQueryLog();
Model::where()->toSql() // output sql query
Recommended:-Laravel Try Catch

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *