User Model:

<?php

namespace App\Models;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Laravel\Sanctum\HasApiTokens;

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var string[]
     */
    protected $fillable = [
        'name',
        'email',
        'password',
    ];

    /**
     * The attributes that should be hidden for serialization.
     *
     * @var array
     */
    protected $hidden = [
        'password',
        'remember_token',
    ];

    /**
     * The attributes that should be cast.
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    public function venues()
    {
        return $this->hasMany(Venue::class);
    }

    public function reviews()
    {
        return $this->hasMany(Review::class);
    }

    public function profile()
    {
        return $this->hasOne(Profile::class);
    }

    public function approvedVenues()
    {
        return $this->hasMany(Venue::class)->where('is_approved', '=', 1);
    }

    public function unapprovedVenues()
    {
        return $this->hasMany(Venue::class)->where('is_approved', false);
    }

    public function ownVenuesReviews()
    {
        return $this->reviews()->whereIn('user_id', function($query) {
            $query->select('id')
            ->from('venues')
            ->whereRaw('venues.user_id = users.id');
        })->get();
    }
}

Venue Model:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Venue extends Model
{
    use HasFactory;

    protected $fillable = [
        'user_id',
        'city_id',
        'category_id',
        'title',
        'address',
        'phone',
        'email',
        'website',
        'facebook',
        'instagram',
        'content_bg',
        'content_en',
        'cover_image',
        'lat',
        'lng'
    ];

    public function user()
    {
        return $this->belongsTo(User::class, 'user_id');
    }

    public function category()
    {
        return $this->belongsTo(Category::class, 'category_id');
    }

    public function city()
    {
        return $this->belongsTo(City::class, 'city_id');
    }

    public function features()
    {
        return $this->belongsToMany(Feature::class, 'venue_feature');
    }

    public function images()
    {
        return $this->hasMany(VenueImage::class);
    }

    public function reviews()
    {
        return $this->hasMany(Review::class);
    }
}

Review Model:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Review extends Model
{
    use HasFactory;

    protected $fillable = ['rating', 'content', 'venue_id', 'user_id'];

    public function venue()
    {
        return $this->belongsTo(Venue::class);
    }

    public function images()
    {
        return $this->hasMany(ReviewImage::class);
    }

    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

So Users have many Venues, Venues have many reviews.

I want to get reviews on own Venues for example (If I own venues with id 100, 101 - I want to get all reviews for these two venues)

Raw query is this:

SELECT * FROM `reviews` WHERE reviews.venue_id IN (SELECT venues.id FROM venues WHERE venues.user_id = 1)

What I tried in Laravel in User model (doesn't work), I'm also curious if there is a way, without raw part:

public function ownVenuesReviews()
{
    return $this->reviews()->whereIn('user_id', function($query) {
        $query->select('id')
        ->from('venues')
        ->whereRaw('venues.user_id = users.id');
    })->get();
}

CodePudding user response：

The raw part is only needed because you have to include the foreign key in the select portion of the sub query. Even though you may not want the user_id in the query result it must still be selected for Laravel to be able to make the relationship match work.

public function ownVenuesReviews()
{
    return $this->reviews()->whereIn('user_id', function($query) {
        $query->select('id', 'user_id')
        ->from('venues');
    })->get();
}

CodePudding user response：

I did it like this, but I'm not quite sure, that this is the best way, I'm open to suggestions:

public function ownVenuesReviews()
{
    return Review::whereIn('venue_id', function($query) {
        $query->select('id')
        ->from('venues')
        ->where('user_id', $this->id);
    })->get();
}