How to use where values ​​from another table?

Question

SQL

Hello, I can't figure out how to compare values ​​from different tables.
I need to receive a Video where the company has the means to pay for the space.
Below is the full context.
Table schema (What needs to be compared is highlighted in color)


Code snippet

return Video::whereHas('company',function (Builder $query) use ($themes){
                $query
                    ->where('status', true)
                    ->whereHas('companySchedules',function (Builder $query){
                        $query->where('weekday',now()->dayOfWeek);
                        $query->where('hour',now()->hour);})
                    ->whereHas('theme', function (Builder $query) use ($themes) {
                        $query->whereIn('id', $themes);}
                    )
                ;
            })->
            whereHas('videoDelay.themes',function (Builder $query) use ($themes){
                    $query->whereColumn('price','<=','company.balance'); //Тут должен подставиться баланс. С числом работает
                    $query->whereIn('id', $themes);}
                )
                ->moderation()
                ->orderByRaw("RAND()")
                ->first() ?? false;

*$themes is an array with id

Model settings:

video models

public function company(): BelongsTo
    {
        return $this->belongsTo(Company::class);
    }

    public function videoDelay(): BelongsTo
    {
        return $this->belongsTo(VideoDelay::class);
    }

company model

public function videos(): HasMany
    {
        return $this->hasMany(Video::class);
    }

VideoDelay model

public function themes(): BelongsToMany
    {
        return $this->belongsToMany(Theme::class)->withPivot(['price']);
    }

    public function videos(): HasMany
    {
        return $this->hasMany(Video::class);
    }

Theme model

public function videoDelay(): BelongsToMany
    {
        return $this->belongsToMany(VideoDelay::class)->withPivot(['price']);
    }

PS if you have any questions, ask, I will answer promptly

Answer 1

Use join and that's it...