CODEWITHSUNDEEP
laravel
phatx88
phatx88

Reputation: 35

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?

Upvotes: 0

Views: 558

Answers (2)

phatx88
phatx88

Reputation: 35

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

Upvotes: 0

Alberto
Alberto

Reputation: 12939

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

Upvotes: 1

Related Questions