tools

Another part of Querying relations in Laravel will cover such problem:

I want to get SomeModels that have latest RelatedModel (hasMany) matching.. _SOME CONSTRAINT HERE_

Eloquent provides expressive syntax for querying relations that can be used for simple cases: SomeModel::whereHas('relatedModels', function ($q) { ... })->get(). Unfortunately it is not capable of handling the latest RelatedModel part just like that.

Let’s dive into how we can tackle this problem using 2 approaches:

 

  1. The easy way – using Collection methods
  2. The right way – using subqueries

Example use case: we want to get Categories with latest post (relation defined as in how to get latest related model ) having status ‘published’. Assuming Category hasMany Posts, MySQL db.

Collection methods:

1
2
3
4
5
6
7
8
9
 
$allCats = Category::with('latestPost')->get();
 
// using strict operator === ...
$filteredCats = $allCats->where('latestPost.status', 'published');
 
// ... or using loose operator ==
$filteredCats = $allCats->whereLoose('latestPost.status', 'published');
 

where/whereLoose is shorthand method for filter. For more complex constraints use the latter:

1
2
3
4
5
6
7
8
 
$filteredCategories = $allCategories->filter(function ($category) {
   // data_get so we don't need to check if latestPost is present
   $status = data_get($category, 'latestPost.status');
 
   return in_array($status, ['published', 'draft']);
});
 

 

Subquery:

1
2
3
4
5
6
7
8
9
10
11
12
 
Category::whereHas('posts', function ($posts) {
 
  $posts->where('status', 'published')
    ->where('id', function ($sub) {
      $sub->from('posts as sub')
        ->selectRaw('max(id)')
        ->whereRaw('sub.category_id = posts.category_id')
    });
  });
})->with('latestPost')->get()
 

 

Both ways will give us the same result, so let’s consider pros and cons now:

Collection methods:

+ very easy to use, that’s a fact
– they are very heavy on performance and can quickly become unbearably slow.

Raw subquery:

– requires a bit of sql knowledge
– lacks abstraction, requires hardcoding stuff like table names and foreign keys
+ despite subquery with another subquery it will be much faster than working with collection
+ it can be improved and abstracted in fact, so there will be no downsides *

 

With that in mind I encourage you to use the latter solution, unless your db isn’t going to grow much.

 

* improved version will be added here soon.

It's only fair to share...Tweet about this on TwitterShare on RedditPin on PinterestShare on FacebookShare on Google+

Related Post

 Laravel – how to define and use Eloquent Glo... Laravel 4.2 introduced a new way of handling soft deletes by making use of traits and query builder macros wrapped in as a feature, that many people a...
Tweaking Eloquent relations – how to get lat... Have you ever needed to show only single related model from the hasMany relationship on a set of parents? Being it latest, highest or just rand...
How to store clients in database? That is, Party M... Have you ever been building any app that handled client's data, was it e-commerce, crm or anything similar, you surely wondered, how to store it in ...
Querying relations in Laravel 4: nested relation Previously we dealt with simple relations defined in Eloquent models and eager loading them in order to save database queries. Now imagine nest...