OrderBy in Laravel 5

Question

I’m trying to make a a basic twitter-eqs messaging app with Laravel 5.

There are messages and senders. I’m trying to write a query which displays latest messages, the sender, and the total number of messages shown by the sender. The query should only show the latest message for each user. e.g. if one person has sent 6 messages in the last 5 mins, it’d only show the latest one from them.

What I'm trying to do in an Eloquent way is:

1. Query the latest messages, GroupBy Sender
2. Select senders name, Join the Sender's name onto query 1
3. Query count of messages GroupBy Sender, join onto query 2

This process may be fundamentally wrong.

Then, in my view, I'd render: Sender A - (31 messages), Hello this is my message.

TABLES

I have 2 tables

Senders

ID | Name

and

Messages

ID | SenderID | Message | Created_at

QUERY

$latestMessages = Sender::
join('messages', 'messages.sender_id', '=', 'senders.id')
->OrderBy('messages.created_at', 'asc')
->GroupBy('senders.id')
->get();

PROBLEM

This outputs the OLDEST message, not the NEWEST. (also, I can't figure out how to count the messages from each sender and join this).

I've tried changing the "ASC" and to "DESC" to no avail. When I remove the GroupBy, it does show the messages in the desired order (newest to oldest) but I can't work out how to only show 1 per sender.

QUESTION Could somebody explain how to achieve this with Laravel 5, or the floor in my approach and what I should look to do?

If you can explain how query and join the count() in Eloquent, that'd be incredible.

Answer 1

You can achieve this with following:

1. Create and impelement Sender class
2. Create and implement Message class

Sender Model:

namespace App;
use Illuminate\Database\Eloquent\Model;

class Sender extends Model {

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

}

Message Model:

namespace App;
use Illuminate\Database\Eloquent\Model;

class Message extends Model { 

    public function sender() {
        return $this->belongsTo('App\Sender');
    }

    // Create scope http://laravel.com/docs/5.0/eloquent#query-scopes
    public function scopeLatest($query) {
        return $query->orderBy('created_at', 'asc');
    }
}

So now you can get all Senders, loop throught them and display their latest message and total count like this:

$senders = Sender::with('messages')->all();

foreach ($senders as $sender) {
    echo "Total Messages: " . $sender->messages->count();
    echo "<br />";
    echo "Latest message: " . $sender->messages()->latest()->first()->Message;
}