CODEWITHSUNDEEP
phplaravel
Linesofcode
Linesofcode

Reputation: 5903

Laravel With() along side with Where()

Take as example the following eloquent code:

Customers::with('ShippingMode')
    ->with('PaymentMethod')
    ->with('BarCodes')
    ->where(function($query) use ($find)
    {
        $query->where('name', 'like', "%$find%")
            ->orWhere('vat_number', 'like', "%$find%");
    });

Now I would like to add more orWhere conditions but looking into PaymentMethod table, like such:

Customers::with('ShippingMode')
    ->with('PaymentMethod')
    ->with('BarCodes')
    ->where(function($query) use ($find)
    {
        $query->where('name', 'like', "%$find%")
            ->orWhere('vat_number', 'like', "%$find%")
            ->orWhere('PaymentMethod.description', 'like', "%$find%");
    });

But this does not work as it returns the error:

Column not found: 1054 Unknown column 'PaymentMethod.descricao' in 'where clause'

I know that if I use the old joins it works great:

Customers::select('customer.*')
    ->from('customers AS customer')
    ->leftJoin('payment_methods AS payment_method', 'payment_method.id', '=', 'customer.payment_method_id')
    ->where(function($query) use ($find)
    {
        $query->where('name', 'like', "%$find%")
            ->orWhere('vat_number', 'like', '%$find%')
            ->orWhere('payment_method.description', 'like', '%$find%');
    });

PLEASE NOTE: The variable $find can be empty and when it's empty it returns all the results without filter applied, which means I cannot use the whereHas function.

Upvotes: 1

Views: 133

Answers (2)

TsaiKoga
TsaiKoga

Reputation: 13404

orWhereHas allow you to add customized constraints to a relationship constraint:

Customers::with('ShippingMode')
    ->with('PaymentMethod')
    ->with('BarCodes')
    ->where(function($query) use ($find) {
        $query->where('name', 'like', "%$find%")
            ->orWhere('vat_number', 'like', "%$find%");
    })->orWhereHas('PaymentMethod', function($query) use ($find) {
        $query->where('description', 'like', "%$find%");
    });

Upvotes: 1

Remul
Remul

Reputation: 8252

Something like this should work:

use Illuminate\Database\Eloquent\Builder;

Customers::with(['ShippingMode', 'PaymentMethod', 'BarCodes'])
    ->when($find, function (Builder $query, $find) {
        $query->where(function (Builder $query) use ($find) {
            $query->where('name', 'like', "%{$find}%")
                ->orWhere('vat_number', 'like', "%{$find}%")
                ->orWhereHas('PaymentMethod', function (Builder $query) use ($find) {
                    $query->where('description', 'like', "%{$find}%");
                });
        });
    })->get();

With when() it will only apply the filter when $find has a value and it allows you to search on the Customer columns with the where() / orWhere() and the PaymentMethod relationship with orWhereHas().

You can read more about when() and whereHas() here:

when

whereHas

Upvotes: 0

Related Questions