orWhereHas-雄辩查询中的参数分组-如何在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或awayClub = Arsenal的位置.

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.

此问题源于之前的问题，看来当使用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'

但是，这不是我想要的，因为提及雄辩的声明，俱乐部查询为分组，上面的查询要么选择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'

注意.. club子查询上的括号.

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-雄辩查询中的参数分组-如何在Laravel中做到这一点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！