HanDyMango

These are some fundamental Eloquent queries as well as some complex ones that I always tend to look up...

How to use offset and limit during query

  // SELECT * FROM `table` offset 5 limit 10
  $blogs = MangoBlog::where('status', 'A')->orderBy('created_at')->skip(5)->take(10)->get();

  // get the 2nd record
  $blogs = MangoBlog::where('status', 'A')->orderBy('created_at')->skip(1)->first();

Nested Where SQL Clauses
Nested "Where Clauses" can be achieved in Laravel Eloquent by using a constraint as follows

  // SELECT * FROM `table` WHERE `x` AND (`y` OR `z`)
  $blogs = MangoBlog::where('status', 'A')->where(function ($query) use ($name) {
            $query->where('title', 'like', '%' . trim($q) . '%')
                  ->orWhere('content', 'like', '%' . trim($q) . '%');
  });

  // SELECT * FROM `table` WHERE `x` OR (`y` AND `z`)
  $blogs = MangoBlog::where('status', 'A')->orWhere(function ($query) use ($name, $age) {
             $query->where('name', $name)->where('age', '>=', $age);
  });

Where Exists SQL Clauses

  // SELECT * FROM `blog` WHERE exists (select 1 from `comments` where comments.blog_id = blog.id and comments.user_id = current user and status > 0)
  $blogs = MangoBlog::whereHas('comments', function ($query) use ($user)  {
             $query->where('user_id', $user->id)
                   ->where('status', '>=', 0);
  })->get();;

Where In SQL Clause
The "Where In" clause can be achieved in Laravel Eloquent by using an array

  // SELECT * from `table` where category_id in ('kids', 'mango', house')
  $blogs = MangoBlog::whereIn('category_id', array('kids', 'mango', 'house')->get();

  // SELECT * from `table` where category_id not_in ('kids', 'mango', house')
  $blogs = MangoBlog::whereNotIn('category_id', array('kids', 'mango', 'house')->get();

  // here's a way to covert the query result to an array so it can be used in WhereIn clause
  $blog_ids = MangoBlog::where('sticky', true)->lists('id');
  $blogs = MangoBlog::whereIn('id', $blog_ids)->get();

Query involving NULL in the where clause

  // SELECT * from `table` where category_id is null
  $blogs = MangoBlog::whereNull('category_id')->get();

  // SELECT * from `table` where category_id is NOTnull
  $blogs = MangoBlog::whereNotNull('category_id')->get();

Randomly select 5 comments (child records) for a given blog article

  // SELECT * from `table` order by RAND() limit 5
  $comments = MangoComment::where('blog_id', $blog_id)->orderByRaw('rand()')->limit(5)->get();

Eager Loading
Child/related records can be eager loaded as long as the relationship is defined in the model

  $blogs = MangoBlog::with('comments')->orderBy('published', 'desc')->limit(5)->get();

Eager Loading with Constraints to modify the query used to retrieve the eager loaded records

  $blogs = MangoBlog::with(array(
      'comments' => function ($query) {
        $query->whereNotNull('verified_date')->orderBy('comment_date', 'desc');
      }
  ))->orderBy('published', 'desc')->limit(5)->get();