filter records from two tables using Laravel Eloquent

Question

I have two table shopping_sessions and cart_addresses. cart_addresses is linked with shopping_sessions using foreign key. shopping_sessions table has various columns eg. company_id, is_active, order_number, and so on.

ShoppingSession Model

class ShoppingSession extends Model
{
    ............
    public function cartAddresses()
    {
        return $this->hasMany(CartAddress::class, 'shopping_session_id');
    }
}

Requirement

If a query string is passed for search ($q), it should either match with "order_number" column in shopping_sessions table or "address_1" column in cart_addresses table of a company that the user is logged in. $companyId has an integer value.

I did tried with the following code.

$query = $this->shoppingSessionModel->query()
    ->with('cartAddresses');


if ( ! empty($q)) {
    //search by order number
    $query->where('order_number', 'LIKE', "%$q%");
    
    //search by shipping address
    $query->orWhereHas('cartAddresses', function($q2) use ($q) {
        $q2->where('address_1', 'LIKE', "%$q%")
            ->where('is_shipping', 1);
    });
}

$query->where('company_id', $companyId)
    ->where('is_active', 0)
    ->whereNotNull('order_number');

return $query->orderBy('updated_at', 'DESC')
    limit);

What is Wrong in this?

When a search query is passed, it retrieves all COMPANY Records matching the text but I want to filter records of single company only. I want to add filter search query and company_id as well.

Issue In short:

I want to fetch records of single company but it is returning records of all company.

Any help would be very much appreciated.

PS: When I debug the SQL query using toSql() it output the following

select * from `shopping_sessions` where (`order_number` LIKE ? or exists (select * from `cart_addresses` where `shopping_sessions`.`id` = `cart_addresses`.`shopping_session_id` and `address_1` LIKE ? and `is_shipping` = ?) and `company_id` = ? and `is_active` = ? and `order_number` is not null) and `shopping_sessions`.`deleted_at` is null order by `updated_at` desc  

Answer 1

Try like this:

if ( ! empty($q)) {
    //search by order number

  $query->where(function ($query) use ($q) {
   
    $query->where('order_number', 'LIKE', "%$q%");
    
    //search by shipping address
    $query->orWhereHas('cartAddresses', function($q2) use ($q) {
        $q2->where('address_1', 'LIKE', "%$q%")
            ->where('is_shipping', 1);
    });
  });

}

I think the issue is that the orWhereHas is being triggered when the companyId is not matched, so if you group the orWhereHas it should fix it.