Eloquent, Compare a value to a column in a related table

Hi everyone,

I'm new to Laravel and now am trying to work with Eloquent queries. However, I'm having trouble understanding how to compare a value in related table.

The application i'm trying to build contains three tables for now: Users, Services, and Reviews. The schema's look as following: User(id, name), Services(id, name, user_id, price), Reviews(id, content, user_id, service_id)

User Model

public function service()
{
    return $this->hasOne('App\Service');
}

public function reviews()
{
    return $this->hasMany('App\Review');
}

Service Model

public function reviews()
{
    return $this->hasMany('App\Review');
}

Review Model

public function user()
{
    return $this->belongsTo('App\User');
}

public function service()
{
    return $this->belongsTo('App\Service');
}

Now I would like to get all the Reviews where the user_id = authenticated user or where the related Services have a user_id = authenticated user.

I tried something like this...

$user_id = Auth::user()->id;
$reviews = App\Review::where('user_id', $user_id)->orWhere.........->get();

However, after the 'orWhere' i'm clueless how to set the needed condition.

Hope there is someone out there that may have a solution or can explain to me what i'm doing wrong. Many thanks in advance.

tykus

7 years ago

Level 104

This should get the reviews you want:

$reviews = Review::whereHas('service', function ($query) {
    return $query->where('user_id', auth()->id());
})->orWhere('user_id', auth()->id())
    ->get();

MickeyMike

7 years ago

Level 1

Hi Tykus.

Thank you so much! it's working great.

I don't know if you know the answer, but what is the reason that I can't replace the auth()->id() in the function with my own variable $user_id, retrieved from $user_id = Auth::user()->id?

I've tried to replace it and both Auth::user()->id and auth()->id() are working; however, when using $user_id i get an error that it is undefined.

tykus

7 years ago

Best Answer

Level 104

You need to pass any variables from the outer scope into the Closure:

$reviews = Review::whereHas('service', function ($query)  use ($userId) {
    return $query->where('user_id', $userId);
})->orWhere('user_id', $userId)
    ->get();

MickeyMike

7 years ago

Level 1

Works like a charm! Many thanks Tykus for your support

Please or to participate in this conversation.