How to query one-to-many relation with condition on main model

I know it is possible and maybe I had already done it, I need to refresh my memory. Let say we have one-to-many

$post->comments

and I want Comments on this post where comment date is greater than specific date. I don't want to get this specific date in php value. Because I want to use it as generic column name.

$myComments = $post->comments->where('published', '>=', 'posts.updated_at');

I do someething like but I am getting

Column not found

thanks

Solution

You've got 3 problems here:

First, $post->comments is a Collection, and does not use SQL at all, so posts.updated_at will not be available, but $post->updated_at would be.

Second, Relationships don't use joins, so posts.updated_at is not going to be available unless you manually join it.

Third, ->where() is a string match, so your query is literally WHERE published = 'projects.updated_at', which will return 0 results. You'd need to use ->whereColumn() there.

You can solve this by "Eager Loading" Comments with that where() clause:

$post = Post::with(['comments' => function ($query) {
  return $query->select('comments.*')
  ->join('posts', 'comments.post_id', '=', 'posts.id')
  ->whereColumn('comments.published', '>=', 'posts.updated_at');
})->first();

// Or `$posts = Post::with([ /* ... */ ])->get();

Or keep the ->comments syntax, but pass the attribute, not the SQL field:

$myComments = $post->comments->where('published', '>=', $post->updated_at)

Or, finally, use ->comments() with the same syntax as Eager Loading:

$myComments = $post->comments()
->select('comments.*')
->join('posts', 'comments.post_id', '=', 'posts.id')
->whereColumn('comments.published', '>=', 'posts.updated_at')
->get();