Laravel Eloquent Query using Where, With, Where and orWhere condition

I am trying to make a Laravel Eloquent query which will find me orders by order number and product name.
I have 3 tables orders, order_details & products.

orders table

id    |   user_id    |    order_id    |    order_cart_id
---------------------------------------------------------
1     |      1       |  123-456-7890  |          1
2     |      1       |  789-456-3210  |          2

order_details table

id    |   order_id   |    cart_id     |        p_id 
---------------------------------------------------------
1     |      1       |        1       |          22
2     |      1       |        1       |          42
3     |      1       |        1       |          2
4     |      1       |        1       |          32
5     |      1       |        1       |          423
6     |      2       |        2       |          432

products table

id    |    p_name
--------------------
1     |      ABC       
2     |      CAD       
42    |      PRO       
22    |      XYZ
32    |      IND    
423   |      MP
432   |      ETC

Orders Model

public function order_cart_products()
{
    return $this->hasManyThrough(AppModelsProductsProduct::class,
        AppModelsOrdersOrderDetail::class,
        'cart_id',
        'id',
        'cart_id','p_id');
}

Order Controller

$orders = Order::where('user_id', auth()->user()->id)
    ->where('order_id', 'LIKE', '%'.$search.'%')
    ->with('order_cart_products', function ($query) use ($search) {
        $query->orWhere('p_name', 'LIKE', '%'.$search.'%');
    })
    ->get()->toArray();

What I am trying to do is by searching order_id(123-456-7890) I need to get all its details with products in it and if I search via p_name(ABC). I need to get all orders in which ABC has been listed. But I don’t understand where am I going wrong am not getting desired output.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Try something like below:

$orders = Order::with('order_cart_products')
    ->where('user_id',auth()->user()->id)
    ->where(function ($query) use ($search) {
        $query->where('order_id', 'LIKE', '%'.$search.'%')
            ->orWhereHas('order_cart_products', function ($query) use ($search) {
                $query->where('p_name', 'LIKE', '%'.$search.'%')
            })
    })
    ->get();

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0