How can I combine this into a single query in Eloquent or RAW?

Question

How can I combine the two queries in to one so it only hits the database once?

I'm tracking the id for comments that initiated a thread (opener comments). The opener_id for the reply comments are the opener comment's id so it's not it's own id.

I do this so I can easily keep track of the opener comments and load replies separately. Similar to how Youtube does it.

This is what I have:

// get the parent node's opener_id
$r = Comments::select('opener_id')
    ->where('id', '=', $parent_id)
    ->first();

// create reply comment
$c = Comments::firstOrNew([
    'parent_id' => $parent_id, // this is the reply comments immediate parent
    'opener_id' => $r->opener_id, // this is the first comment that started the thread (opener comment)
    'topic' => $topic,
    'username' => $username,
    'comment' => $comment
]);
$c->save();

Probably not needed but my model looks like this:

protected $fillable = [
    'id',

    'parent_id',
    'opener_id',
    'topic',
    'username',
    'comment',

    'created_at',
    'updated_at'        
];

Everything works perfectly as is. I just want to combine the 2 queries so it hits the database once instead of two times as it currently does for better performance.

Answer 1

I understand from you that you are trying to create something similar to youtube's commenting system.

You can use sub query like here, but laravel not support in sub query out-of-the-box.
Also, IMHO this micro optimization is looking unnecessary, the code readability and choose the right schema is more important.

Maybe I'm not see the whole picture but I'd consider change your schema a little, like changing parent_id column with order (number that present the comment number)

I recommend you to open a question in softwareengineering or codereview groups and put there the whole picture so you can get a comprehensive and full answer.