Laravel Migration: How to add a column with a default of an existing column

In this post, we will see how to add a new column to an existing table by setting the default value the same as the existing column.

What would be the context? In my case, I had to resolve the problem to sort the list of posts that has the latest activity, for example, comments.

We can, of course, resolve this problem by using eloquent whereHas & sortBy clauses. But this was affecting the performance, therefore I decided to add a new field last_activity_at to the posts table, which will be default equivalent to the created_at but then will be updated whenever a new Comment is added to the Post.

Let’s see how I did it.

Migration:

Schema::table('posts', function(Blueprint $table) {
    $table->dateTime('last_activity_at');
});

\DB::statement('UPDATE posts SET last_activity_at = created_at');

Model Comment booted hook :
I am updating Post last_activity_at every time a new comment is created.

protected static function booted()
{
    try {
        static::created(function (Comment $comment) {
            $post = $comment->post;
            $post->last_activity_at = $comment->created_at;
            $post->save();
        });
    } catch (\Exception $exception) {
        // handle the exception
    }
}

That’s it, now I can sort my posts by last_activity_at.

Hope it would be helpful, see in the next post.