'Laravel orWhere with left join

I'm not that good with Laravel Eloquent, so be cool pls.

I have 1 product table and 4 "target" tables (sales, novelties, liquidations and exposure).

I'm trying to get all products that have at least one of the four "targets" with this query:

return Product::leftjoin('sales', 'sales.product_id', '=', 'products.id')
        ->leftjoin('liquidations', 'liquidations.product_id', '=', 'products.id')
        ->leftjoin('noveltys', 'noveltys.product_id', '=', 'products.id')
        ->leftjoin('exposures', 'exposures.product_id', '=', 'products.id')
        ->select('products.*', 'sales.*', 'liquidations.*', 'noveltys.*', 'exposures.*')
        ->where('noveltys.novelty_end_at', '!=', null)
        ->orwhere('sales.sale_end_at', '!=', null)
        ->orWhere('liquidations.liquidation_end_at', '!=', null)
        ->orWhere('exposures.exposure_end_at', '!=', null)
        ->get();

Problem is, it only return products that have the first "where" clause true. (In other word, currently, this query only return produtcs with a not null novelty_end_at, it doesn't return products with a sale_end_at and others).

How can I achieve that it also return products with a not null sale_end_at, exposure_end_at and a liquidation_end_at ?

Thank you!

phplaraveleloquent


Solution 1:[1]

I found the solution, it's almost the same thing, execpt that I'm using orWhereNotNull and that I'm using DB::table at the start instead of Product::leftjoin directly.

 return DB::table('products')
            ->leftjoin('sales', 'sales.product_id', '=', 'products.id')
            ->leftjoin('liquidations', 'liquidations.product_id', '=', 'products.id')
            ->leftjoin('noveltys', 'noveltys.product_id', '=', 'products.id')
            ->leftjoin('exposures', 'exposures.product_id', '=', 'products.id')
            ->whereNotNull('sales.sale_end_at')
            ->orwhereNotNull('noveltys.novelty_end_at')
            ->orwhereNotNull('liquidations.liquidation_end_at')
            ->orwhereNotNull('exposures.exposure_end_at')
            ->get();

Thank you!

Solution 2:[2]

seems a syntax mistake

->orwhere('sales.sale_end_at', '!=', null)

should be

->orWhere('sales.sale_end_at', '!=', null)

also you can try using orWhereNotNull

like

return Product::leftjoin('sales', 'sales.product_id', '=', 'products.id')
        ->leftjoin('liquidations', 'liquidations.product_id', '=', 'products.id')
        ->leftjoin('noveltys', 'noveltys.product_id', '=', 'products.id')
        ->leftjoin('exposures', 'exposures.product_id', '=', 'products.id')
        ->select('products.*', 'sales.*', 'liquidations.*', 'noveltys.*', 'exposures.*')
        ->whereNotNull('noveltys.novelty_end_at')
        ->orWhereNotNull('sales.sale_end_at')
        ->orWhereNotNull('liquidations.liquidation_end_at')
        ->orWhereNotNull('exposures.exposure_end_at')
        ->get();

Solution 3:[3]

Perhaps you could use ->orWhereHas() method. To use this function, you must define the relationship in your Sales and the four target Model classes. Documentation: https://laravel.com/docs/9.x/eloquent-relationships

Then in your query,

return Product::whereHas('noveltys', function(Builder $query) {
                  // your where clause
              })
              ->orWhereHas('sales', function(Builder $query) {
                  // your where clause
              })
              ->orWhereHas('liquidations', function(Builder $query) {
                  // your where clause
              })
              ->orWhereHas('exposures', function(Builder $query) {
                  // your where clause
              })
              ->get();

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 JT88
Solution 2 Prafulla Kumar Sahu
Solution 3 Elvis

Related Questions