Using multiple where clauses with laravel query builder

Question

I have a lot of problems converting the following SQL query to work with the Laravel query designer.

SELECT * FROM gifts JOIN giftcategory ON gifts.id = giftcategory.giftid JOIN giftoccasions ON gifts.id = giftoccasions.giftid JOIN giftrelationship ON gifts.id = giftrelationship.giftid WHERE (gifts.gender = 'any' OR gifts.gender = 'male') AND giftoccasions.occasionid = '2' AND (giftcategory.categoryid = '0' OR giftcategory.categoryid = '1') AND giftrelationship.relationshipid = '1' 

This query works fine, but I cannot get the same results when using the Laravel query builder. So far I have the following code. This does not work correctly at all. I think the problem may be related to the orWhere part, because it seems to return results that do not match any of the other where clauses.

 $giftQuery = DB::Table('gifts') ->Join('giftcategory', 'gifts.id', '=', 'giftcategory.giftid') ->Join('giftoccasions', 'gifts.id', '=', 'giftoccasions.giftid') ->where('gifts.gender', '=', "male") ->orwhere('gifts.gender', '=', "any") ->where('giftoccasions.occasionid', '=', "2") ->where('giftoccasions.relationshipid', '=', "1") ->Where('giftcategory.categoryid', '=', "0") ->orWhere('giftcategory.categoryid', '=', "1"); 

Answer 1

You want to use advanced where with a grouping of parameters:

 $giftQuery = DB::table('gifts') ->join('giftcategory', 'gifts.id', '=', 'giftcategory.giftid') ->join('giftoccasions', 'gifts.id', '=', 'giftoccasions.giftid') ->where(function($query) { $query->where('gifts.gender', '=', "male") ->orWhere('gifts.gender', '=', "any"); }) ->where('giftoccasions.occasionid', '=', "2") ->where('giftoccasions.relationshipid', '=', "1") ->where('giftcategory.categoryid', '=', "0") ->orWhere('giftcategory.categoryid', '=', "1");