Dan Hastings Dan Hastings -4 years ago 132
PHP Question

Using multiple where clauses with laravel query builder

Im having a lot of trouble converting the following SQL query to work with laravels query builder.

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 cant get the same results when using Laravels query builder. I have the following code so far. It is not working correctly at all. Im thinking the issue could lie with the orWhere part because it seems to be returning results that dont 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 Source

You want to use advanced where with parameter grouping:

$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");
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download