cakephp habtm join issue

Question

I have table structure like below:

events (boxing, sparring etc)
competitors (users who are participating into diff events)
events_competitors (every competitor's selected events will go here)

Now what i want is, as per my match schedules i want to find competitors from schedules tables, where each competitors will be matching with events they have selected at registration time.

I'm using a find query something like this:

$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('EventCompetitor.event_id = '.$current_matchsc['Event']['id']),
                                                          ) 
                                                        )
                                )
);

Here, I have used joins because I am not able to find relations from EventCompetitor matching with Competitors and Events.

Problem: The single matching record comes 10 times, because of join while it should be single time only.

Earliest reply would be appreciated.

Thanks in advance!

Now a next level once this worked for me:

I have two levels of conditions check, in EventsCompetitors I want to see, if they want to have a fight with black belt or if they are minor and they want to have fight with adults so in this case, I want to see these both flags and on that basis, again my conditions will be changed and a new results will be displayed for my additional displays.

Kindly let me know, if sometime anyone have idea on this kind of stuffs with CakePHP.

Again a lot thanks to stackoverflow for their excellent services !

Answer 1

If you are only interested in a single record and are always sure that the others are duplicates then you can use find('first', ...) instead.

If you are interested in multiple different records but you are getting multiples of each of those records you could add in a 'group' => array('') to aggregate the competitors on a specific field to only return them once ?