CODEWITHSUNDEEP
laravel
johnW
johnW

Reputation: 329

How to get past comments of a user? Column not found error

I have a UserController that has the index() method that should get all the past comments in posts by a user and for each comment get the post details (title and date). So I have the code below to get the past comments of a user:

$pastComments = $user->comments()->with('post')->where('create_date', '<', now())->get();

The create_date is a field of the posts table, not comments table.

But it appears an error:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'create_date' in 'where clause' (SQL: select * from `comments` where `comments`.`user_id` = 1 and `comments`.`user_id` is not null and `create_date` < 2018-05-08 15:04:47)".

Do you know why?

Models:

class User extends Authenticatable
{
    public function posts(){
        return $this->hasMany('App\Post', 'user_id');
    }
    public function comments(){
        return $this->hasMany('App\Comment','user_id');
    }
}

class Comment extends Model
{
    public function user(){
        return $this->belongsTo('App\User');
    }
    public function post(){
        return $this->belongsTo('App\Post');
    }

}


class Post extends Model
{
    public function admin(){
        return $this->belongsTo('App\User', 'user_id');
    }
    public function comments(){
        return $this->hasMany('App\Comment', 'post_id');
    }
}

Tables structure:

Users table: id, name, ...
Comments table: id, post_id, user_id, ...
Posts table: id, name, user_id, ...

With:

$comments = $user->comments()->with('post')->whereHas(['post' => function ($query) {$query->where('create_date', '<', now()); }])->get();

It appears:

  strpos() expects parameter 1 to be string, array given

Upvotes: 0

Views: 56

Answers (2)

Rwd
Rwd

Reputation: 35190

If you want to scope a query by its relationship you'll need to use whereHas e.g.

$pastComments = $user->comments()->with('post')
        ->whereHas('posts', function ($query) {
            $query->where('create_date', '<', now());
        })
        ->get();

https://laravel.com/docs/5.6/eloquent-relationships#querying-relationship-existence

Upvotes: 1

linktoahref
linktoahref

Reputation: 7972

Constraining Eager Loads

$pastComments = $user->comments()->with(['post' => function ($query) {
    $query->where('create_date', '<', now());
}])->get();

Upvotes: 1

Related Questions