Why isn't CakePHP using SQL joins

I'm fairly new to CakePHP, and have run into an issue that I can't seem to find a good solution for. My application has the following associations:

  • Member hasMany Vote
  • Roll hasMany Vote
  • Vote belongsTo Member, Roll

I'd like to retrieve all of the votes cast by a member and the roll associated with each vote. The following code produces the perfect result:

$this->Member->id = $id;
$this->Member->contain(array('Vote' => 'Roll'));
$this->set('member_data', $this->Member->read());

Unfortunately, this code is also very inefficient. Instead of just doing something like SELECT * FROM votes JOIN rolls ON votes.roll_id = rolls.id WHERE votes.member_id = 100, Cake does the following:

  1. SELECT * FROM votes WHERE member_id = 100
  2. SELECT * FROM rolls WHERE roll_id = 1
  3. SELECT * FROM rolls WHERE roll_id = 2
  4. SELECT * FROM rolls WHERE roll_id = 3
  5. SELECT * FROM rolls WHERE roll_id = 4
  6. And so on--a big problem when over 500 rolls match!

How would I make this more efficient while still using the read() method?


I think you can try from vote model, try this code:

$this->Vote->recursive = 0;

Need Your Help

What mysql driver do I use with spring/hibernate?

java mysql hibernate spring

Little confused, is 'driverclassname' and 'hibernate.dialect' both referring to the mysql driver?

jmap/jhat vs jVisualVM

java performance memory-management profiling heap

I understand that jmap is used to create heap dumps and the same can be analysed by Jhat.