CODEWITHSUNDEEP
sqllaravellaravel-4
Hasan
Hasan

Reputation: 2552

How to retrieve all users with their last post

I am trying to get my users (authors actually, there will be maximum of 5-6 authors) with their one last post to show in a sidebar in the homepage. Since they will be listed in the homepage I am trying to reduce the amount of sql queries due to performance issues. I have tried;

$users=User::with(array('posts'=>function($query){

        $query->take(1);
    }))->get();

However it gets only one post in total, not one for every user. And my sql knowledge is limited.

How can I solve my poblem using Eloquent ORM, Query Builder or raw sql query?

Upvotes: 3

Views: 1330

Answers (2)

Jason Lewis
Jason Lewis

Reputation: 18665

A solution is to define a hasOne relationship on the User model ordering by the posts created_at column.

public function lastPost()
{
    return $this->hasOne('Post')->orderBy('created_at', 'desc');
}

Then your query would be as such.

$users = User::with('lastPost')->get();

To limit the columns you can constrain the query either at the relationship level:

return $this->hasOne('Post')->select('id', 'user_id', 'title', 'created_at')->orderBy('created_at', 'desc');

Or when you use the with method:

$users = User::with(['lastPost' => function($query)
{
    $query->select('id', 'user_id', 'title', 'created_at');
})->get();

Note that you need the user_id and created_at columns as well, as they're required for the WHERE and ORDER BY clauses in the query.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270713

The general SQL way to do this is:

select p.*
from posts p join
     (select p.authorid, max(created_at) as maxdate
      from posts p
      group by p.authorid
     ) psum
     on p.authorid = psum.authorid and p.created_at = psum.maxdate

This assumes that there is are no duplicates.

Depending on the database you are using, there are definitely other ways to write this query. That version is standard SQL.

Upvotes: 1

Related Questions