orWhereHas - Eloquent 查询中的参数分组 - 我如何在 Laravel 中执行此操作? [英] orWhereHas - parameter grouping on Eloquent query - how do I do this in Laravel?

问题描述

在我正在构建的一个雄辩的查询中，我使用 Laravel 4.1 的 whereHas 和 orWhereHas 方法对 has 关系进行了约束.

In an eloquent query I am building, I am placing a constraint on a has relationship using Laravel 4.1's whereHas and orWhereHas methods.

在示例足球应用程序中，我希望对 homeClub 和 awayClub 关系进行约束，以便结果集将选择 homeClub = Arsenal ORawayClub = 阿森纳.

In the example soccer application, I wish to place a constraint on the homeClub and awayClub relationships so that I can the result set will select where the homeClub = Arsenal OR awayClub = Arsenal.

这个问题是从早期问题演变而来的code>orWhereHas 方法 - 生成的 sql 不分组 or 约束.

This issue has evolved from an earlier question It seems that when using the orWhereHas method - the resulting sql doesn't group the or constraint.

查询(放置约束的相关摘录):

The query (relevant excerpt that is placing the constraints):

$ret
        ->where( function( $subquery ) use ( $ret ){
            $ret->whereHas('homeClub', function ( $query ){
                $query->where('name','Arsenal' );
            })->orWhereHas('awayClub',function ( $query ){
                $query->where('name','Arsenal' );
            });
        })
        ->where( function ( $subquery ) use ( $ret, $parameterValues ){
            $ret->whereHas('season', function ($query) use ( $parameterValues ){
                $query->where('name', $parameterValues['season_names'] );

            });
        } )
        ->whereHas('territory',function( $query ) use ( $parameterValues ){     
               $query->where('region','Australia');

        })->get()->toArray();

这会产生 sql:

SELECT * FROM `broadcasts` WHERE 

(SELECT count(*) FROM `uploads` WHERE `broadcasts`.`upload_id` = `uploads`.`id` and `type` = 'international-audience') >= '1' 
and 
(SELECT count(*) FROM `clubs` WHERE `clubs`.`id` = `broadcasts`.`home_club_id` and `name` = 'Arsenal') >= '1' 
or 
(SELECT count(*) FROM `clubs` WHERE `clubs`.`id` = `broadcasts`.`away_club_id` and `name` = 'Arsenal') >= '1' 
and 
(SELECT count(*) FROM `seasons` WHERE `broadcasts`.`season_id` = `seasons`.`id` and `name` = '2012/13') >= '1' 
and 
(SELECT count(*) FROM `territories` WHERE `broadcasts`.`territory_id` = `territories`.`id` and `region` = 'Australia') >= '1'

但是，这不是我想要的，因为参考雄辩的陈述，俱乐部查询是 grouped 并且上面的查询要么选择 homeClub 约束，要么选择 awayClub、赛季名称、领土区域.我想要的是以下 SQL:

But, this isn't what I want, because referring to the eloquent statement, the club queries are grouped and the query above either selects the homeClub constraints OR, the awayClub, season name, territory region. What I'm intending is the following SQL:

SELECT * FROM `broadcasts` WHERE 

(SELECT count(*) FROM `uploads` WHERE `broadcasts`.`upload_id` = `uploads`.`id` and `type` = 'international-audience') >= '1' 
and 
((SELECT count(*) FROM `clubs` WHERE `clubs`.`id` = `broadcasts`.`home_club_id` and `name` = 'Arsenal') >= '1' 
or 
(SELECT count(*) FROM `clubs` WHERE `clubs`.`id` = `broadcasts`.`away_club_id` and `name` = 'Arsenal') >= '1' )
and 
(SELECT count(*) FROM `seasons` WHERE `broadcasts`.`season_id` = `seasons`.`id` and `name` = '2012/13') >= '1' 
and 
(SELECT count(*) FROM `territories` WHERE `broadcasts`.`territory_id` = `territories`.`id` and `region` = 'Australia') >= '1'

注意.. 俱乐部子查询上的括号.

Note.. the parentheses on the club subquery.

有谁知道我将如何将其写成雄辩的查询?我真的不想恢复到流利/加入.

Does anyone know how I would write this as the eloquent query? I really don't want to have to revert to fluent / joins.

推荐答案

你需要引用传递给 where 闭包的查询.否则，您将绕过任何分组将分组的 where 子句添加到主查询:

You need to reference the query passed through to the where closure. Otherwise you are adding the grouped where clauses to the main query bypassing any groupings:

$ret
->where( function( $query ){
    $query->whereHas('homeClub', function ( $subquery ){
        $subquery->where('name','Arsenal' );
    })
    ->orWhereHas('awayClub',function ( $subquery ){
        $subquery->where('name','Arsenal' );
    });
})
->where( function ( $query ) use ( $parameterValues ){
    $query->whereHas('season', function ($subquery) use ( $parameterValues ){
        $subquery->where('name', $parameterValues['season_names'] );
    });
})
->whereHas('territory',function( $query ) use ( $parameterValues ){     
    $query->where('region','Australia');
})
->get();

这篇关于orWhereHas - Eloquent 查询中的参数分组 - 我如何在 Laravel 中执行此操作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！