CakePHP multiple JOIN findAll Conditions issue

Question

Here is my complex (atleast i think it is complex) condition to find competitors from matches schedules and relating to events.

Now I have HTBTM relations with events_competitors table, where multiple events have multiple competitors users entries.

Here, I have used joins condition for joining and getting related events with competitors which works fine, but I also want to apply additional conditions, for is_black (check for black belt) and is_adult (check for adult person)

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

Here I want only those competitors which have both conditions (is_black / is_adult) 0, means not eligible, but it does not applying the same, which is resulting in wrong competitors results.

Below is my whole find condition:

$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'

                                  )

                             );

Any idea, how can i get those things applied into JOIN conditions, so it is applied into results.

Thanks !

Below is SQL Dump for your ref:

SELECT 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 FROM competitors AS Competitor left JOIN event_competitors AS EventCompetitor ON (EventCompetitor.event_id = 3 AND EventCompetitor.is_black = 0 AND EventCompetitor.is_adult = 0) LEFT JOIN ranks AS Rank ON (Competitor.rank_id = Rank.id) LEFT JOIN tournaments AS Tournament ON (Competitor.tournament_id = Tournament.id) LEFT JOIN countries AS Country ON (Competitor.country_id = Country.id) LEFT JOIN states AS State ON (Competitor.state_id = State.id) LEFT JOIN degrees AS Degree ON (Competitor.degree_id = Degree.id) LEFT JOIN schools AS School ON (Competitor.school_id = School.id) LEFT JOIN transactions AS Transaction ON (Transaction.competitor_id = Competitor.id) WHERE Competitor.status = 1 AND Competitor.payment_completed = 1 AND Competitor.weightgroup_id = 13 AND Competitor.rank_id = 11 AND Competitor.degree_id = '0' AND Competitor.gender = 'Female' GROUP BY Competitor.id

Here is the left join condition from above query for ref:

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

Answer 1

You should be using the containable behavior for this. More at: http://book.cakephp.org/view/1323/Containable

1. Add it to your Competitor model.

var $actsAs = array('Containable');

1. Update your model relationships in your Competitor model to include the is_black and is_adult conditions:

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

3) To inject the event id, pass a contain array to your find operation:

$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']
        )
    )
);

If is_black and is_adult are not always required for the relationship, you would want to move those conditions from the model and pass them in via the contain parameter of the find operation as needed.