Laravel: Order query using relationship

Hello guys. Please, I need help with the following issue. I have two tables, user and contents. users has many contents and content belongs to user.

I want to query:

All users with their contents ordered by the users with the most recent content
users with their contents that were uploaded within the last 24hrs

Any suggestion with working code samples will be appreciated.

tisuchi

5 years ago

Level 70

@fcode

User.php

    public function contents()
    {
        return $this->hasMany(Content::class)->orderBy('updated_at');
    }

Content.php

    public function user()
    {
        return $this->belongsTo(User::class);
    }

Now, in the controller, you can write the query like this way-

// Getting all latest contents of user ID = 1
$contents = User::with('contents')->findOrFail(1);

// Getting all latest contents of user ID = 1
$contentsInLast24Hours = User::with('contents', function ($query) {
    $query->where('created_at', '>=', now()->subDay(1));
})->findOrFail(1);

1 like

MichalOravec

5 years ago

Best Answer

Level 75

This could help you

https://reinink.ca/articles/ordering-database-queries-by-relationship-columns-in-laravel

$users = User::with('contents')->whereHas('contents', function ($query) {
    $query->where('created_at', '>=', now()->subDay());
})->orderByDesc(Content::select('created_at')
    ->whereColumn('contents.user_id', 'users.id')
    ->latest()
    ->take(1)
)->get();

fcode

5 years ago

Level 7

Thanks, @michaloravec for the article. I wonder why Google did not find the article during my googling

Please or to participate in this conversation.