Thread: Trying to get data from a directly unrelated table

The issue I have is probably a simple one but I am just not getting it and can't find anything on the web to help me.

What I have is 3 tables, Table1(PotentialLoan) is related to Table2(SalesPerson) and Table2 is related to Table3(Dealer). Table1 is not related directly to Table3. What I am trying to do is fill out a form, submit it and then create a print page from the data just entered. My print page requires information from Table3 based on the association with Table2.

What is the easiest way to get the information from Table3?

The latest attempt looks like this

Code:

public function reply($id = null) {
        $this->PotentialLoan->id = $id;
        if (!$this->PotentialLoan->exists()) {
            throw new NotFoundException(__('Invalid potential loan'));
        }
        $this->set('potentialLoan', $this->PotentialLoan->read(null, $id));
        $conditions = array('dealer.id' => $potentialLoan['PotentialLoan']['salesPerson_id']);
        $this->set('dealer', $this->Dealer->find('first', array('conditions' => $conditions)));
        //$dealer = $potentialLoan['PotentialLoan'][
    }

There are a couple different ways you could expect to get back the exact data that you need.

1.) If you only wanted to make one database call, you could setup your query with joins, and fields along with your conditions. It would look something like this:

Code:

$this->PotentialLoad->recursive = -1;
$joins = array(
  array(
    'table'=>'sales_people',
    'alias'=>'SalesPerson',
    'type'=>'LEFT',
    'conditions'=>array(
      'SalesPerson.id = PotentialLoad.salesPerson_id'
    )
  ),
  array(
    'table'=>'dealers',
    'alias'=>'Dealer',
    'type'=>'LEFT',
    'conditions'=>array(
      'Dealer.id = SalesPerson.dealer_id'
    )
  )
);

$fields = array('PotentialLoan.*', 'SalesPerson.*', 'Dealer.*');
$conditions = array('PotentialLoad.id'=>$potentialLoan['PotentialLoan']['id']);
$results = $this->PotentialLoan->find('all', array('joins'=>$joins, 'fields'=>$fields, 'conditions'=>$conditions));

2.) Alternatively, if you only needed data from the dealers table, you could access that table directly (assuming you have properly setup relationships in the model) by doing the following:

Code:

$dealer = $this->PotentialLoan->SalesPerson->Dealer->findById($dealer_id);

I hope this helps.