Fetching data from multiple tables in laravel5

Question

I have two tables posts and sharedposts. sharedposts table has columns "user_id", "updated_at" and "post_id" where as posts table has 'id', 'title' and 'body'. I know about the relations to link both tables. All i want to do is to display each post ordered by 'updated_at' from both the tables. Please help with both query builder and eloquent way. Thanku

Answer 1

The code is not tested, but you can try this:

Post::whereHas('sharedpost',function($query){
    $query->orderBy('updated_at');
})->get()

Answer 2

Maybe a bit different approach if there won't be to much of the updates of the models? This way I suppose the read should be faster (somebody please correct me if I'm wrong here)

Add another field to the table by creating new migration and adding

$table->timestamp('last_updated');

Then using Events update the field whenever the Post or SharedPost is updated by adding this to AppServiceProvider:

public function boot()
{
    Post::saving(function ($post) {
        $post->last_updated = Carbon::now();
    });
}

I'm not sure if this will work though and it would be appreciated if you returned a feedback as I'm lazy to try it out myself at the moment :). Worse case scenario you might need to change the method as:

public function boot()
{
    SharedPost::saved(function ($sharedpost) {
        $sharedpost->post->last_updated = Carbon::now();
        $sharedpost->save();
    });
}

or you could check out Observers if you don't want the code in the AppServiceProvider. Haven't used it but it seems straightforward.

Then you could simply Post::orderBy('last_updated')->get();

This way you will have one or two more inputs to your database but I think (and guys please correct me if I'm wrong) the get() should be faster. Keep in mind I'm far from a MySQL expert.

And in the end, you can make it even faster by saving this data to Cache using Redis.