Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

ufodisko's avatar

Help convert query builder into Eloquent

Simply put, I am trying to retrieve all the favorites a user has made. Then fetch the associated reply on which the favorite has been made, then the associated thread in which the reply has been made, then the channel in which thread has been made.

I tried setting up eloquent relations but I've failed so I resorted to a query builder, which works as you'd expect.

$favorites = DB::table('favorites')
            ->join('replies', 'favorites.favorited_id', '=', 'replies.id')
            ->join('threads', 'replies.thread_id', '=', 'threads.id')
            ->join('channels', 'threads.channel_id', '=', 'channels.id')
            ->select('favorites.*', 'replies.*', 'threads.*', 'channels.*')
            ->get();

Here's what I tried to do initially.

In my User model

public function favorites()
    {
        return $this->hasMany(Favorite::class)->latest();
    }

In my Favorite model

public function owner() {
        return $this->belongsTo(User::class, 'user_id');
    }

    public function replies()
    {
        return $this->belongsTo(Reply::class, 'id');
    }

Then in Reply model

public function favorites()
    {
        return $this->hasMany(Favorite::class, 'favorited_id');
    }

so now when I return return $user->favorites I get the associated favorites with that user indeed.

id  1
user_id 551
favorited_id    501
favorited_type  "App\Reply"
created_at  "2019-06-22 23:14:47"
updated_at  "2019-06-22 23:14:47"

In this case, the user has only made 1 favorite on a reply.

However, when I try to return $user->favorites->replies I get the following error

Property [replies] does not exist on this collection instance.

On top of all that, when I try to favorite a reply now I get this error

SQLSTATE[HY000]: General error: 1364 Field 'favorited_type' doesn't have a default value (SQL: insert into `favorites` (`user_id`, `favorited_id`, `updated_at`, `created_at`) values (551, 11, 2019-06-22 23:50:06, 2019-06-22 23:50:06))

So I'm not really sure what to do at this point.

0 likes
2 replies
ufodisko's avatar

I just solved it by eager loading replies and threads in User model

public function favorites()
    {
        return $this->hasMany(Favorite::class)->with('replies.threads')->latest();
    }
ufodisko's avatar

But now whenever I add a new thread and go back to my profile page, I see that the numbers of queries have increased by 1. It's already resting at 20 queries as im loading a bunch of other stuff, but you could see how this could explode if the user has hundreds of threads,

I'm not seeing the N+1 problem when I make a new comment or favorite a reply, and I'm pulling those data to the profile page as well.

Please or to participate in this conversation.