Laravel Eloquent Retrieve Records Which Has Null Fields Included Relationship

Question

I am trying to get records which has empty fields with relationship. And my Deal model like that.

<?php

namespace App\Models;

class Deal extends Model
{
use HasFactory;
use SoftDeletes;

protected $fillable = [
    'id',
    'creator_user_id',
    'user_id',
    'owner_name',
    'person_id',
    'person_name',
    'stage_id',
    'title',
    'value',
    'currency',
    'formatted_value',
    'status',
    'won_time',
    'note',
    'source',
    'gender',
    'nationality',
    'city',
    'language',
    'passport_number',
    'arrival_airport',
    'arrival_flight_code',
    'arrival_date',
    'arrival_time',
    'hotel',
    'hotel_nights',
    'hotel_check_in_date',
    'hotel_check_out_date',
    'departure_airport',
    'departure_flight_code',
    'departure_date',
    'departure_time',
    'discount'
];

public function person()
{
    return $this->hasOne('App\Models\Person','id','person_id');
}

/**
 * Scope a query to all fields not filled.
 *
 * @param  \Illuminate\Database\Eloquent\Builder  $query
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function scopeNotFilled($query): \Illuminate\Database\Eloquent\Builder
{
    return $query
        ->orWhereNull('source')
        ->orWhereNull('gender')
        ->orWhereNull('nationality')
        ->orWhereNull('city')
        ->orWhereNull('language')
        ->orWhereNull('arrival_airport')
        ->orWhereNull('arrival_flight_code')
        ->orWhereNull('arrival_date')
        ->orWhereNull('arrival_time')
        ->orWhereNull('hotel')
        ->orWhereNull('hotel_nights')
        ->orWhereNull('hotel_check_in_date')
        ->orWhereNull('hotel_check_out_date')
        ->orWhereNull('departure_airport')
        ->orWhereNull('departure_flight_code')
        ->orWhereNull('departure_date')
        ->orWhereNull('departure_time')
        ;
   }
}

When i try to get data with this query which is bellow

         return Deal::notFilled()->
         whereHas('person',function ($query){
            $query->orWhere('email',null);
         })
        ->where('won_time','>',Carbon::parse('2021-01-01'))
        ->orderBy('won_time', 'desc')
        ->paginate(15);

I can get deals which have some empty fields but it doesn't include relations which doesn't have email. I want to get deals which have empty fields and also if relation doesn't have email address must be in the list. How can i solve this problem ?

Answer 1

I have solved my problem with join like that;

    return Deal::where('won_time','>',Carbon::parse('2021-01-01'))->
    join('people','people.id','=','deals.person_id')->
    where(function($q){
       $q->notFilled()
       ->orWhereNull('people.email');
    })->
    orderBy('won_time', 'desc')->
    select('deals.*')->
    paginate(15);

If there is any efficient way to solve this problem i can hear your solutions.