Using multiple where clauses with laravel query builder

Question

I'm having a lot of trouble converting the following SQL query to work with Laravel's 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 can't get the same results when using Laravel's query builder. I have the following code so far. It is not working correctly at all. I'm thinking the issue could lie with the orWhere part because it seems to be returning results that don't 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");

if you're happy with my answer below, you can accept it :)

Limon Monte
– Limon Monte

2015-04-06 07:42:24 +00:00
Commented Apr 6, 2015 at 7:42 — Limon Monte
– Limon Monte, Commented Apr 6, 2015 at 7:42

Limon Monte · Accepted Answer · 2015-03-30 09:04:25Z

20

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

edited Mar 30, 2015 at 9:04

answered Mar 30, 2015 at 8:58

Limon Monte

54.8k49 gold badges191 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using multiple where clauses with laravel query builder

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related