2

I have two tables (and corresponding Models) as following:

posts (
    id            auto_increment,
    category_id   refrences_id_on_categories,
    data          json
    date          timestamp
)

and

categories (
    id            auto_increment,
    name          text
)

I could get 5 posts using:

Post::orderBy('category_id', 'asc')->take(5)->get();

But, Is there any way to get 5 posts of each Category using Eloquent (Laravel 5, dbms: postgres)?

Edit i'm looking for a one line solution such as using GROUP BY or other clauses.

3 Answers3

1

You can use Eager Loading to reduce your queries:

$categories = Category::with('posts')->get();

foreach ($categories as $category)
{
    $posts = $category->posts()->take(5)->get();
}
Jake Opena
  • 1,475
  • 1
  • 11
  • 18
  • 1
    Eager loading will not limit the posts being retrieved from the database, so it's not really an improvement as far as performance goes, because you'll get all categories with all posts for each category. – Bogdan Jun 06 '15 at 14:34
0

If you setup a one-to-many relation between you Category and Post models, you can use that to get 5 posts for each category this way:

$categories = Category::all();

foreach ($categories as $category)
{
    $posts = $category->posts()->take(5)->get();
}

The relation in your Category model would look like this:

class Category extends Model {

    public function posts()
    {
        return $this->hasMany('App\Post');
    }

}
Bogdan
  • 43,166
  • 12
  • 128
  • 129
  • Thanks, but i'm looking for a one line solution using `GROUP BY` or something else. –  Jun 06 '15 at 13:59
  • Using `GROUP BY` will return one row for the grouped column, so you won't get what you're after (which I'm guessing is a nested array of results for each category). Could you please explain why you're keen on doing that, perhaps explain how you think you'd be able to do that without Eloquent (keep in mind, not all solutions to a problem can be one liners, event if that feels more elegant, and also Eloquent has its limitations). – Bogdan Jun 06 '15 at 14:05
  • Suppose if I had many categories, so getting the result in one query would perform better than that in many queries. –  Jun 06 '15 at 14:10
  • 1
    I understand your performance considerations, but what you want can't be done with Eloquent in one query. It takes some doing even without it, just with the Query Builder. Have a look at this [Laravel forum post](http://laravel.io/forum/04-05-2014-eloquent-eager-loading-to-limit-for-each-post) to get a better understanding why that is (it's the same case but with users and comments). – Bogdan Jun 06 '15 at 14:26
0

Based on this answer, I used raw queries using DB::select() as following:

$sql = "SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY date DESC) AS r,
    t.*
  FROM
    posts t) x full outer join categories t2
  on x.category_id = t2.id
WHERE
  x.r <= 5";

DB::select($sql);
Community
  • 1
  • 1