Fetching and filtering relations in Laravel Eloquent

@chiefsucker And the relations are...

I thought this was obvious, sorry.

Groups have many threads, threads have many comments, and users are owners of comments.

@chiefsucker Obvious things are not always obvious in the same way to everyone ;)

// Group model
public function comments()
{
  return $this->hasManyThrough('Comment', 'Thread'); // adjust namespaces for the models
}

// then
$userComments = $group->comments()
   ->where('owner_id', $userId) // or comments.owner_id in case of ambiguity
   // you may want to eager load the threads
   // ->with('thread')
   ->get();

@JarekTkaczyk: I tried this approach before and had issues (empty collections). I debugged it a little bit more and found the culprit:

$comments = $group->comments->where('owner_id', $id);

While this works:

$comments = $group->comments->where('owner_id', intval($id));

My method header looks like this:

/**
 * Remove a user from a group and wipe all of its comments.
 *
 * @param  Group $group
 * @param  int $id
 *
 * @return Response
 */
public function getRemoveGroupMemberAndWipeComments(Group $group, $id)

Do you have any idea why I can’t just pass $id?

@chiefsucker Check your post again, I think you made a mistake. Anyway, I didn't use $group->comments->... but $group->comments()-> - it is huge difference. First returns the collection, second returns queryable relation object.

@chiefsucker And btw yes, I know why you can't use $id:

$id; //probably a string like '5'
$group->comments->where('owner_id', $id); // empty
$group->comments->whereLoose('owner_id', $id); // Bang! correct result

This is because where on the collection uses strict comparison === by default.

Thanks, it solved my issue, I just had to add the table name because of ambiguity as you mentioned in your comment.

What is the difference in performance / executed SQL queries between both ways:

# Through collection.
$comments = $group->comments->where('owner_id', intval($id));
# Through relation.
$comments = $group->comments()->where('threads_comments.owner_id', $id)->get();

$comments = $group
  ->comments // calls the relation, queries the comments table for all related rows and returns collection
  ->where('owner_id', intval($id)); // filters the collection and returns new one with matching items

$comments = $group
  ->comments() // returns relation object, no query yet
  ->where('owner_id', $id) // where clause on the query builder
  ->get() // get only matching rows

So in the context of SQL there is probably unnoticeable difference. On the PHP/memory part the difference is quite big (relatively), but I wouldn't worry about it either unless you have thousands of related rows.

Thanks again. It looks like solution #2 is the way to go. I just discovered Laravel Debugbar which looks good for analyzing SQL queries from Eloquent.

I have another problem now. I actually just want to delete all comments from a specific user, so I replaced get() with delete():

$group->comments()->where('threads_comments.owner_id', $id)->delete();

Now I have the problem that the updated_at field is ambiguous and I somehow would need Eloquent to use threads_comments.updated_at instead. I actually found a thread where you also answered and that mentions the same issue.

Does this mean that I either can continue to use this solution with a loop and one statement per comment, or I’ll have to use the Query Builder instead?

@chiefsucker So it's soft deleting, right?

Yep.

@chiefsucker For learning the Eloquent artisan tinker and playing in cli is the way to go. Debugbar is not that good for this, but of course it's useful for debugging :)

Anyway, to achieve what you wanted I would do this:

$commentsToDelete = $group->comments()
        ->where('thread_comments.owner_id', $id)
        ->select('thread_comments.id')  // you need this in order to use lists below
        ->lists('id');

Comment::whereIn('id', $commentsToDelete)->delete();

2 queries, short and easy piece of code.

@JarekTkaczyk: Your solution worked as expected. You can’t imagine how thankful I am.

It didn’t only solve my problem, but I also learned more about Eloquent’s innards through this thread than through reading the official docs and tinkering by myself in the last couple of days.

@chiefsucker Glad I could help.