Laravel query builder how to ignore certain column

I having trouble to build a query to filter out all products with column hidden == false, the logic is if the attribute hidden is ticked it will not select these ‘hidden’ row.

Here my code:

$products = DB::table('view_product')
            ->join('brand', 'view_product.brand_id', '=', 'brand.id')
            ->join('category', 'view_product.category_id', '=', 'category.id')
            ->select('view_product.*', 'brand.name as brand_name','category.name as category_name')
            ->orwhere("brand.name", "LIKE", "%$search%")
            ->orWhere("category.name", "LIKE", "%$search%")
            ->orWhere("view_product.name", "LIKE", "%$search%")
            ->whereNotIn('hidden' , true)
            ->paginate(9);

How do I write this query so it can filter out ‘hidden’ row?

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

please try this:

$products = DB::table(function($query) use (...){
     return $query->join('brand', 'view_product.brand_id', '=', 'brand.id')
        ->join('category', 'view_product.category_id', '=', 'category.id')
        ->select('view_product.*', 'brand.name as brand_name','category.name as category_name')
        ->orwhere("brand.name", "LIKE", "%$search%")
        ->orWhere("category.name", "LIKE", "%$search%")
        ->orWhere("view_product.name", "LIKE", "%$search%");
 }, 'view_product')->where('hidden' , false)->paginate(9);

because you need a subquery for that ->where('hidden' , false) otherwise the orWhere will make the condition true

Method 2

This code works for me

$products = DB::table(function($query){
                return $query
                ->select('*')
                ->from('view_product')
                ->where('hidden' , false);                
            }, 'view_product')
                    ->join('brand', 'view_product.brand_id', '=', 'brand.id')
                   ->join('category', 'view_product.category_id', '=', 'category.id')
                   ->select('view_product.*', 'brand.name as brand_name','category.name as category_name')
                   ->orwhere("brand.name", "LIKE", "%$search%")
                   ->orWhere("category.name", "LIKE", "%$search%")
                   ->orWhere("view_product.name", "LIKE", "%$search%")
                   ->paginate(9);

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