0

I have user profiles that allow users to leave messages for each other. (Think Facebook/MySpace)...

Given a profile_messages table with the following fields id, user_id, author_id, parent_id, and message, how would I efficiently display them in a threaded layout?

Note: The comments will only be 1 level deep.

Currently, I'm fetching all of the relevant comments, then rebuilding the collection to have a $messages with a sub-collection of replies on each item. 

$messages = new Collection();
$replySets = [];

foreach ($user->profileMessages as $message)
{
    $parentId = $message->parent_id;

    if ($parentId == 0)
    {
        $messages->put($message->id, $message);
    }
    else
    {
        $replySets[$parentId][] = $message;
    }
}

foreach ($replySets as $parentId => $replies)
{
    $parent = $messages->get($parentId);

    if (isset($parent))
    {
        if ( ! isset($parent->replies))
        {
            $parent->replies = new Collection();
        }

        foreach ($replies as $reply)
        {
            $parent->replies->push($reply);
        }
    }
}

// Pass $messages to the view


This works great. However, I can't help but to think there is a better way of doing this... Is there a better way to do this, or perhaps a way to make use of the relationships to get a result set matching the $profileMessage->replies structure?

user1960364
  • 1,951
  • 6
  • 28
  • 47

1 Answers1

3

From your description I assume you have root messages that have user_id pointing to the user, a message was sent to. And replies to those root messages, that are relevant not to the user_id but to the parent_id.

So:

$user->load('messages.replies.author');

foreach ($user->messages as $message)
{
  $message; // root message
  $message->replies; // collection
  $message->replies->first(); // single reply
  $message->replies->first()->author;
}

with relations like below:

// user model
public function messages()
{
  return $this->hasMany('Message')->where('parent_id', '=', 0);
  // you could also create RootMessage model with global scope instead of this where
}

// message model
public function replies()
{
  return $this->hasMany('Message', 'parent_id');
  // again, here you can create separate Reply model if you like
}

public function author()
{
  return $this->belongsTo('User', 'author_id');
}

public function user()
{
  return $this->belongsTo('User');
}
Jarek Tkaczyk
  • 78,987
  • 25
  • 159
  • 157
  • Thanks, I was thinking something along those lines but kept running into the +1 issue so I knew I wasn't doing something right. I'll give this a go and see if it's what I'm looking for. :) – user1960364 Nov 19 '14 at 20:32
  • Eager loading to the rescue with n+1. And yes, this is what you're looking for :) – Jarek Tkaczyk Nov 19 '14 at 20:53
  • Hmm, I'm still running into the n+1 issue regardless of how I try to load them... I've posted the changes I made based on your code here: http://laravel.io/bin/xK0WB – user1960364 Nov 19 '14 at 21:33
  • Well, no you're not running into n+1 issue ;) You just load too many things there, but I wouldn't worry about that. n+1 would mean that you run a query for every entry, but that's not the case here. You just have `user` relation and `author` relation refering to the same `User`, but still it requires 2 queries. Like in my answer - I suppose you don't need `user` relation loaded on the `replies`, for they refer to another message, not to the user himself, right? – Jarek Tkaczyk Nov 19 '14 at 22:47
  • Ahh, you're right about the User being redundant. However, it seems that my trying to access `$message->replies` in the view is making Eloquent attempt to fetch them when they don't already exist in the collection. That's where the last two `select * from profile_messages` queries are coming from... – user1960364 Nov 19 '14 at 23:37
  • Sorry, I was using a recursive view which would try to load past a single depth, so what I needed was `@if (isset($message->replies))` instead of `@if ($message->replies->count())` since the latter will executea query if the relation doesn't already exist. :D Thanks for the help! – user1960364 Nov 20 '14 at 00:03