How to change CakePHP 2 Model associations to deep linking with bindModel and custom finder?

Question

I am trying to replicate this SQL query result which works:

SELECT r.id, r.day, s.ddbroute_id, s.delivery_id, d.id, d.laststatusid, t.id, t.delivery_id, t.statusstage_id, st.id, st.stage
FROM ddbroutes r
LEFT JOIN ddbrouteslots s on r.id = s.ddbroute_id
LEFT JOIN deliveries d on s.delivery_id = d.id
LEFT JOIN trackingstatuses t on d.laststatusid = t.id 
LEFT JOIN statusstages st on t.statusstage_id = st.id

I am using CakePHP 2 Models with

1. bindModel to change Model associations on the fly
2. custom Find put the logic in a Model

There is no common field from the bottom table beyond the second level. The error message is: 'Model "Ddbroute" is not associated with model "Delivery".' I therefore tried it with and without Delivery in the 'contain' array, and neither way brought in the Delivery fields. I would be happy to use joins if appropriate. I have read most relevant posts on StackOverflow that I could find.

My code with further information is below. Any help gratefully received.

---

I have five tables (including the following fields):

ddbroutes (id, day)
ddbrouteslots (id, ddbroute_id, delivery_id)
deliveries (id, laststatusid)
trackingstatuses (id, statusstage_id)
statusstages (id, stage)

There are the following relationships set up in the Models:

Ddbroute hasMany Ddbrouteslot (Ddbrouteslot belongsTo Ddbroute)
Delivery hasOne Ddbrouteslot (Ddbrouteslot belongsTo Delivery)
Delivery hasMany Trackingstatus (Trackingstatus belongsTo Delivery)
Statusstage hasMany Trackingstatus (Trackingstatus belongsTo Statusstage)

Though Delivery hasOne Ddbrouteslot (and this will be hasMany - revised - this is now staying as hasOne), for any individual Ddbroute, there is only one Delivery associated which each Ddbrouteslot. Containable is set up in all the Models. I didn't know if I needed to use unbindModel first (it didn't change the error message).

My code in Ddbroute.php Model file (only as far as the Delivery table)

public $findMethods = array('ddbstatuses' => true);

  protected function _findDdbstatuses($state, $query, $results = array()) {
      if ($state === 'before') {
        $ddbrouteslotmodel = ClassRegistry::init('Ddbrouteslot');
        $ddbrouteslotmodel->unbindModel(
          array('belongsTo' => array('Delivery'))
        );
        $ddbrouteslotmodel->bindModel(
          array('hasOne' => array(
            'Delivery' => array(
              'className' => 'Delivery',
              'foreignKey' => 'id',
              'dependent' => false,
              'fields' => array(
                'id', 'laststatusid'
              )
              )
            ))
          );

          $deliverymodel = ClassRegistry::init('Delivery');
          $deliverymodel->unbindModel(
            array('hasOne' => array('Ddbrouteslot'))
          );
          $deliverymodel->bindModel(
            array('belongsTo' => array(
              'Delivery' => array(
                'className' => 'Delivery',
                'foreignKey' => 'delivery_id'
                )
              )
            )
          );

          $query['contain'] = array(
            'Ddbrouteslot', 'Delivery'
          );
        return $query;
      }
        return $results;
    }

In another controller, to run the find operation:

$this->LoadModel('Ddbroute');
$ddbstatuses = $this->Ddbroute->find('ddbstatuses');
$this->set(compact('ddbstatuses')); // to make available in a view

I also had a further attempt with a long join array, but the query did not bring in any Delivery, Trackingstatus or Statusstage information, though the query seems to have run.

  public $findMethods = array('ddbstatuses' => true);

  protected function _findDdbstatuses($state, $query, $results = array()) {
  if ($state === 'before') {

    ClassRegistry::init('Delivery'); // not sure these three lines were needed so I tried with and without them
    ClassRegistry::init('Trackingstatus');
    ClassRegistry::init('Statusstage');



    $query['joins'] = array(
      array(
        'table' => 'ddbrouteslots',
        'alias' => 'Ddbrouteslot',
        'type' => 'LEFT',
        'conditions' => array(
            'Ddbroute.id = Ddbrouteslot.ddbroute_id'
      )),
      array(
        'table' => 'deliveries',
        'alias' => 'Delivery',
        'type' => 'LEFT',
        'conditions' => array(
          'Ddbrouteslot.id = Delivery.id'
      )),
      array(
        'table' => 'trackingstatuses',
        'alias' => 'Trackingstatus',
        'type' => 'LEFT',
        'conditions' => array(
          'Delivery.laststatusid = Trackingstatus.id'
      )),
      array(
        'table' => 'statusstages',
        'alias' => 'Statusstage',
        'type' => 'LEFT',
        'conditions' => array(
          'Trackingstatus.statusstage_id = Statusstage.id'
      ))
  );


    $query['contain'] = array(
        'Ddbrouteslot',
          'Delivery',  // Not sure I should be adding these other models, so I tried with and without them
          'Trackingstatus',
          'Statusstage'
      );
    return $query;
  }
  return $results;
}

Answer 1

After some help, I now have four solutions to get my data, though really three of them are variants on the first one. I am relatively inexperienced and there were some basic things I didn't appreciate.

1. IN A CONTROLLER

$this->LoadModel("Ddbrouteslot");
$res = $this->Ddbrouteslot->find("all", array(
  "conditions" => array(
    "Ddbrouteslot.delivery_id > 0",
    "Ddbrouteslot.ddbroute_id" => 45
),
"contain" => array(
    "Ddbroute",
    "Delivery" => array(
"Trackingstatus" => array(
   "order" => array(
   "Trackingstatus.id" => "desc"
    ),
    "limit" => 1,
    "Statusstage"
   )
  )
 )
);

Timings from DebugKit: main query was 20ms; Trackingstatus and Statusstage were additional queries of 18ms each x 4 for four associated deliveries; total time was 164ms. This is quite slow which is not ideal.

This started from the second model, Ddbrouteslot, because this had direct relationships with both Ddbroute and Delivery. There were no changes to any of the associations. The belongsTo relationship from Ddbrouteslot to Delivery worked fine. There was already a hasMany relationship between Delivery and Trackingstatus on delivery_id.

---

2. USING SQL

$this->LoadModel("Ddbroute");
$qres = $this->Ddbroute->query(
    "SELECT *
    FROM 
    ddbroutes AS r
    LEFT JOIN ddbrouteslots s on r.id = s.ddbroute_id
    LEFT JOIN deliveries d on s.delivery_id = d.id
    LEFT JOIN trackingstatuses t on d.laststatusid = t.id 
    LEFT JOIN statusstages st on t.statusstage_id = st.id
    WHERE s.delivery_id > 0 AND s.ddbroute_id = 45
;"
debug($qres);

Timings: this took 19ms. This means it was much faster. This is not recommended in the Cake documentation, and clearly it is not as portable between databases as a pure Cake find.

---

3. CHANGING THE BASE MODEL

$rres = $this->Ddbroute->find("all", array(
    "conditions" => array(
    "Ddbroute.id" => 45
),
"recursive" => -1,
"contain" => array(

        "Ddbrouteslot" => array(
            "conditions" => array(
                "Ddbrouteslot.delivery_id > 0"
            ),
            "Delivery" => array(
                "Trackingstatus" => array(
                    "order" => array(
                        "Trackingstatus.id" => "desc"
                    ),
                    "limit" => 1,
                    "Statusstage"
                )
            )
        )
    )
));
debug($rres);

Timings: Main query was 18ms; Delivery, Trackingstatus and Statusstage were 18ms each x 4 for four associated deliveries; total time was 234ms. It was slower because Delivery needed to be run for each despatch because it was not within the model of Ddbroute. Changing recursive didn't make a difference.

---

4. USING A CUSTOM FIND This was the same query as 1.) above, but just with a custom find method.

public $findMethods = array('ddbstatuses' => true);   
protected function _findDdbstatuses($state, $query, $results = array()) {
    if ($state === 'before') {       
        $query['conditions'] = array(
          "Ddbrouteslot.delivery_id > 0",
          "Ddbrouteslot.ddbroute_id" => 45
        );
        $query['contain'] = array(
          "Ddbroute",
          "Delivery"=> array(
             "Trackingstatus" => array(
                "order" => array(
                "Trackingstatus.id" => "desc"
             ),
             "limit" => 1,
                "Statusstage"
              )
           )
          );
         return $query;
      }   
   return $results;
}