蛋糕PHP多个JOIN查找所有条件问题

Question

这是我从比赛时间表和与事件相关的比赛中寻找竞争对手complex (atleast i think it is complex)条件。

现在我与events_competitors表有HTBTM关系，其中多个事件有多个竞争对手用户条目。

在这里，我使用了joins条件来加入和获得相关events competitors工作正常，但我还想应用其他条件，用于is_black(检查黑带(和is_adult(检查成年人(

'EventCompetitor.is_black' => 0,
'EventCompetitor.is_adult' => 0,

在这里，我只希望那些同时具有两个条件(is_black/is_adult(的竞争对手为0，意味着不符合条件，但它不适用相同，这会导致错误的竞争对手结果。

以下是我的完整查找条件：

$matchdivisions = $this->Competitor->find("all" , 
                               array(
                                'conditions' => 
                                   array(
                   'Competitor.status' => 1, 
                   'Competitor.payment_completed' => 1,
                   'Competitor.weightgroup_id' => $current_matchsc['Matchschedule']['weightgroup_id'],
                   'Competitor.rank_id' => $current_matchsc['Matchschedule']['rank_id'],
                   'Competitor.degree_id' => $current_matchsc['Matchschedule']['degree_id'],
                   'Competitor.gender' => $current_matchsc['Matchschedule']['gender'],
                                    ),
                                    'joins' => 
                                      array(
                                        array(
                                           'table' => 'event_competitors',
                                           'alias' => 'EventCompetitor',
                                           'type' => 'left',
                                           'conditions'=> array(
                                                "AND" =>array(
                        'EventCompetitor.event_id = '.$current_matchsc['Event']['id'],
                        'EventCompetitor.is_black' => 0,
                        'EventCompetitor.is_adult' => 0,
                                                      )
                                                 ),
                                           )
                                     ),
                                    'group' => 'Competitor.id'
                                  )
                             );

任何想法，我怎样才能将这些东西应用到JOIN条件下，所以它被应用到结果中。

谢谢！

下面是您的参考的 SQL 转储：

选择Competitor 。id ， Competitor .first_name，Competitor。last_name ， Competitor .parent_name ， Competitor .gender ， Competitor .date_of_birth ， Competitor .email_address ， Competitor .weight，Competitor。weightgroup_id ， Competitor .height，Competitor。rank_id，Competitor。degree_id ， Competitor .photo，Competitor。school_id，Competitor。years_of_experience，Competitor。age ， Competitor .tournament_id ， Competitor .total_registration_fees，Competitor。address1，Competitor。address2 ， Competitor .city ， Competitor .zip_code，Competitor。country_id，Competitor。state_id，Competitor。phone_number，Competitor。mobile_number ， Competitor .payment_mode ， Competitor .email_sent ， Competitor .payment_completed ， Competitor .status，Competitor。created ， Competitor .modified ， Rank .id，Rank。name ， Rank .status，Rank。created，Rank。modified ， Tournament .id，Tournament。tournament_name，Tournament。tournament_type ， Tournament .tournament_date ， Tournament .venue_name ， Tournament .address1 ， Tournament .address2，Tournament。city，Tournament。zip_code，Tournament。country_id，Tournament。state_id，Tournament。created ， Tournament .modified ， Country .id，Country。name，Country。status，Country。created，Country。modified ， State .id，State。country_id，State。name，State。short_name ， State .status，State。created ， State .modified，Degree。id ， Degree .rank_id，Degree。name ， Degree .status ， Degree .created，School。id，School。name，School。address1 ， School .address2，School。city ， School .zip_code，School。country_id，School。state_id，School。phone_number，School。owner_name，School。establishment_date，School。total_competitors ， School .status ， School .created，School。modified ， Transaction .id，Transaction。competitor_id ， Transaction .noncompetitor_id，Transaction。created，Transaction。modified，Transaction。mc_gross ， Transaction .address_status，Transaction。payer_id ， Transaction .address_street，Transaction。payment_date，Transaction。payment_status，Transaction。address_zip，Transaction。first_name ， Transaction .address_country_code ， Transaction .address_name，Transaction。custom，Transaction。payer_status，Transaction。address_country，Transaction。address_city ， Transaction .payer_email，Transaction。verify_sign，Transaction。txn_id ， Transaction .payment_type ， Transaction .last_name，Transaction。address_state ， Transaction .receiver_email，Transaction。item_name，Transaction。mc_currency，Transaction。item_number，Transaction。residence_country，Transaction。transaction_subject，Transaction。payment_gross ， Transaction .shipping，Transaction。test_ipn，Transaction。pending_reason从competitors作为Competitor左加入event_competitors作为EventCompetitor(EventCompetitor。event_id = 3 和 EventCompetitor .is_black = 0 和 EventCompetitor .is_adult = 0( 左联接ranks为Rank ( Competitor .rank_id = Rank .id ( 左加入tournaments，如Tournament 上 ( Competitor .tournament_id = Tournament .id ( 左加入countries作为Country ( Competitor .country_id = Country .id ( 左加入states作为State ( Competitor .state_id = State .id ( 左加入degrees作为Degree ( Competitor .degree_id = Degree .id ( 左加入schools作为School ( Competitor .school_id = School .id ( 左加入transactions作为Transaction ( Transaction .competitor_id = Competitor .id ( Competitor .status = 1 和 Competitor .payment_completed = 1 和 Competitor .weightgroup_id = 13 和 Competitor .rank_id = 11 和 Competitor .degree_id = '0' 和 Competitor .gender = "女性"分组按Competitor .id

这是上面查询中 ref 的左连接条件：

left JOIN event_competitors AS EventCompetitor ON (EventCompetitor.event_id = 3 AND EventCompetitor.is_black = 0 AND EventCompetitor.is_adult = 0)

Answer 1

您应该为此使用可包含的行为。 更多内容： http://book.cakephp.org/view/1323/Containable

1. 将其添加到您的竞争对手模型中。

var $actsAs = array('Containable');

1. 更新竞争对手模型中的模型关系，以包含is_black和is_adult条件：

    
var $hasAndBelongsToMany = array(
                'Competitor' => array(
                    'className' => 'Competitor',
                    'joinTable' => 'event_competitors',
                    'alias' => 'EventCompetitor',
                    'conditions' => array(
                        'EventCompetitor.is_black' => 0,
                        'EventCompetitor.is_adult' => 0
                    )
                 )
            );

3( 要注入事件 ID，请将包含数组传递给查找操作：

$contain = array(
    'EventCompetitor' => array(
        'conditions' => array('EventCompetitor.event_id' =>  $current_matchsc['Event']['id'])
    )
);
$matchdivisions = $this->Competitor->find("all" , 
    array(
        'contain' => $contain,
        'conditions' => array(
            'Competitor.status' => 1, 
             'Competitor.payment_completed' => 1,
            'Competitor.weightgroup_id' => $current_matchsc['Matchschedule']['weightgroup_id'],
            'Competitor.rank_id' => $current_matchsc['Matchschedule']['rank_id'],
            'Competitor.degree_id' => $current_matchsc['Matchschedule']['degree_id'],
            'Competitor.gender' => $current_matchsc['Matchschedule']['gender']
        )
    )
);

如果关系并不总是需要is_black和is_adult，则需要将这些条件从模型中移出，并根据需要通过查找操作的 include 参数传入。