Laravel Eloquent Querying


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 = 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);

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();
Go Back to List Page

Leave a comment

Name : Comment : view emoticons
Please consider signing up for our website.
If you sign up and log in:
  •   You can avoid the "I'm not a robot" captcha when commenting
  •   You can also avoid typing your name every time
  •   You can upload a picture for each comment
  •   You can change or delete your comment within 1 hour
  •   You can track all the comments you posted on this site
  •   You can read blog posts that are only open to members
  •   You can look up blogs using the search feature
  •   More privileges for our friends & families coming...

OK, Sign me up!

Emoticons are a great way to visually express how you feel.
However, there are times when unintended content is converted to emoticon because the content happens to have one of the emoticon symbols. That's why it's always good idea to preview your comment before posting and when you see this type of problem, you can indicate NOT to auto convert.