Data from 3 tables, 2 of which are related only through the third

[jholcomb]

Hi,

 

I have a problem in which I have 3 tables (PotentialLoans, SalesPeople, Dealers) the relationships are as follows.

 

PotentialLoans belongsTo SalesPeople

SalesPeople belongsTo Dealers

 

the code I got to work I believe is probably not the best practice for cakePHP the code that works follows

 

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));
	$this->set('dealer', $this->PotentialLoan->query("SELECT d.id, d.name, d.address, d.city, d.state, d.zip, d.phone, d.fax
FROM potential_loans p, sales_people s, dealers d
WHERE p.sales_person_id = s.id
AND s.dealer_id = d.id
AND p.id =" . $id));
}

 

Any help in cleaning this up would be appreciated.

Thank you.

[jcombs_31]

What exactly is your question? What exactly are you trying to accomplish?  What controller are you using?  Why are you using "query" instead of "find"?