在laravel查询构建器中使用多个where子句

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

我在转换以下SQL查询以使用laravels查询构建器时遇到了很多麻烦。

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.

此查询工作正常,但在使用Laravels查询构建器时,我无法获得相同的结果。到目前为止,我有以下代码。它根本不能正常工作。我认为问题可能在于orWhere部分,因为它似乎返回的结果与其他where子句不匹配。

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

1 个解决方案

#1

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