CakePHP & MVC – Potentially superfluous SQL queries when looking up 'names' associated with ids

Question

I've probably murdered the whole concept of MVC somewhere along the line, but my current situation is thus:

I have participants in events and a HABTM relationship between them (with an associated field money_raised). I have a controller that successfully creates new HABTM relationships between pre-existing events and participants which works exactly as I want it to.

When a new relationship is created I wish to set the flash to include the name of the participant that has just been added. The actually addition is done using ids, so I've used the following code:

public function addParticipantToEvent($id = null) {
    $this->set('eventId', $id);

    if ($this->request->is('post')) {
        if ($this->EventsParticipant->save($this->request->data)) {
            $participant_id = $this->request->data['EventsParticipant']['participant_id'];
            $money_raised = $this->request->data['EventsParticipant']['money_raised'];
            $participant_array = $this->EventsParticipant->Participant->findById($participant_id);
            $participant_name = $participant_array['Participant']['name'];
            $this->Session->setFlash('New participant successfully added: ' . $participant_name . ' (' . $participant_id . ') ' . '&mdash; &pound;' . $money_raised);
        } else {
            $this->Session->setFlash('Unable to create your event-participant link.');
        }
    }
}

This works, but generates the following SQL queries:

INSERT INTO `cakephptest`.`cakephptest_events_participants` (`event_id`, `participant_id`, `money_raised`) VALUES (78, 'crsid01', 1024)     1   1   0

SELECT `Participant`.`id`, `Participant`.`name`, `Participant`.`college` FROM `cakephptest`.`cakephptest_participants` AS `Participant` WHERE `Participant`.`id` = 'crsid01' LIMIT 1        1   1   0

SELECT `Event`.`id`, `Event`.`title`, `Event`.`date`, `EventsParticipant`.`id`, `EventsParticipant`.`event_id`, `EventsParticipant`.`participant_id`, `EventsParticipant`.`money_raised` FROM `cakephptest`.`cakephptest_events` AS `Event` JOIN `cakephptest`.`cakephptest_events_participants` AS `EventsParticipant` ON (`EventsParticipant`.`participant_id` = 'crsid01' AND `EventsParticipant`.`event_id` = `Event`.`id`)

This final one seems superfluous (and rather costly) as the second should give me all that I need, but removing $this->EventsParticipant->Participant->findById($participant_id) takes out both the second and third queries (which sort of makes sense to me, but not fully).

What can I do to remedy this redundancy (if indeed I'm not wrong that it is a redundancy)? Please tell me if I've made a complete hash of how these sorts of things should work – I'm very new to this.

score 1 · Accepted Answer · answered Jun 12 '12 at 23:42

1

This is probably due to the default recursive setting pulling the relationship. You can remedy this by setting public $recursive = -1; on your model (beware this will affect all find calls). Or, disable it temporarily for this find:

$this->EventsParticipant->Participant->recursive = -1;
$this->EventsParticipant->Participant->findById($participant_id);

I always suggest setting public $recursive = -1; on your AppModel and using Containable to bring in the related data where you need it.

answered Jun 12 '12 at 23:42

jeremyharris

7,884
22
31

I'm sure I've tried that before and it's had no effect, but I've just tried it again and it does exactly what I want, so I must have done something wrong before. Thanks very much. – rbobbington Jun 13 '12 at 13:02

CakePHP & MVC – Potentially superfluous SQL queries when looking up 'names' associated with ids

1 Answers1