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 !

score 0 · Accepted Answer · answered Apr 06 '11 at 14:43

0

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 ?

answered Apr 06 '11 at 14:43

mark-cs

4,677
24
32

Now few more checks into this condition: There are some additional conditions check, like into **joins** mention in above query 'conditions'=> array( 'EventCompetitor.event_id = '.$current_matchsc['Event']['id'], 'EventCompetitor.is_black' => 0, 'EventCompetitor.is_adult' => 0, ), and on that basis a more filteration of my results, Is that possible? Please let me know. – Aditya P Bhatt Apr 07 '11 at 13:10
I think you should just be able to put them into your conditions array – mark-cs Apr 07 '11 at 13:21

cakephp habtm join issue

1 Answers1

Linked