Laravel orWhere with left join-CodePudding

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!

CodePudding user response：

seems a syntax mistake

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

should be

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

also you can try using orWhereNotNull

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();

CodePudding user response：

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!