Eloqent Cross Table query

Hi folks :-)

I wish to query across tables with an OR relationship but can only work out how to do an AND.

In my code below I wish to find data with a blank email (user table) OR a short phone number (member table). (user is related to member 1 to 1)

Thank you :)

    $users = $users->with('member');

         $users = $users->where(
                function($query) {
                    $query->where('email','=','');
                }
            );
            $users = $users->whereHas('member', function($member) {
                $member->whereRaw('LENGTH(phone1)<10' );  
            });


    $users = $users->filter(request(['search']))
    ->orderBy("users.name")
    ->paginate(20);

LaryAI

2 years ago

Level 58

To query across tables with an OR relationship, you can use the orWhere method instead of where method. Here's how you can modify the code to achieve the desired result:

$users = $users->with('member')
    ->where(function($query) {
        $query->where('email', '=', '')
            ->orWhereHas('member', function($member) {
                $member->whereRaw('LENGTH(phone1) < 10');
            });
    })
    ->filter(request(['search']))
    ->orderBy("users.name")
    ->paginate(20);

In the above code, we have used the orWhere method to combine the two conditions. This will return the records where either the email is blank or the phone number is less than 10 characters long.

1 like

Lozza

2 years ago

Level 4

Perfect :)

Please or to participate in this conversation.