Model associations for CakePHP

0
votes

I'm new to CakePHP and having a bit of trouble figuring out how to set up model associations.

Say I have 3 tables: payments, reservations and reservation_details with the following data

table reservations
id  |  confirmation_number   |   guest_id
1         123                        1

table reservation_details -a reservation can have multiple entries (multiple rooms)
id  |  reservation_id   |   date     |  time   | room_id    |   rate
 2           1            2014-18-04    13:00        1           9.99
 3           1            2014-18-04    13:00        2           4.99

table payments - many payments for one reservation can be made
id  |   reservation_id  |   payment_amount    |  payment_type   |   guest_id
 4            1                    14.98                Cash             1

Here are my model Associations

 //Reservation model
 public $hasMany = array('ReservationDetail', 'Payment');

 //ReservationDetail model
 public $belongsTo = array('Reservation');

 //Payment model
 public $belongsTo = array('Reservation');
 public $hasMany = array('ReservationDetail' => array('foreignKey' => 'reservation_id'));

What I'm trying to do is be able to search for a payment and it would return the corresponding reservation and reservation_details for that payment. So it would grab any records from reservation_details that share the same reservation_id. Right now the reservation is returned, but the reservation_details is returned empty

The following search returns information from payments and reservations, but an empty array from reservation_details.

$payment = $this->Payment->find('all',array(
                                      'conditions' => array(
                                         'Payment.guest_id' => '1'
                                        )
                                ));

I'm almost positive it's joining the reservation_details table on payments.id = payments.reservation_id rather than payments.reservation_id = reservation_details.reservation_id. When I manually change payments.id to 1 (the reservation_id value), then the reservation_details are returned.

I believe the MySQL query that I'm trying to achieve would be something like

SELECT reservations.*, reservation_details.*, payments.* from payments
INNER JOIN reservations on reservations.id = payments.reservation_id
INNER JOIN reservation_details on reservation_details.reservation_id = payments.reservation_ID
WHERE payments.guest_id = '1'
1
phpmysqlcakephp

1 Answers

0
votes

Payment.php

add containable behavior as-

public $actsAs = array('Containable');

PaymentsController.php

$payments = $this->Payment->find('all', array(
                                            'conditions' => array(
                                                'Payment.guest_id' => '1'
                                            ),
                                            'contain' => array(
                                                'Reservation' => array(
                                                    'ReservationDetail'
                                                )
                                            )

));

debug($payments);