StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCakePHP inefficient database queries: can they be avoided?
text
Body
copied!<p>My table structure:</p> <pre><code>boxes (id, boxname) boxes_items (id, box_id, item_id) </code></pre> <p>I was looking at the SQL logs for the "delete box" action, and am slightly horrified.</p> <pre><code>SELECT COUNT(*) AS count FROM boxes Box WHERE Box.id = 191 SELECT BoxesItem.id FROM boxes_items BoxesItem WHERE BoxesItem.box_id = 191 SELECT COUNT(*) AS count FROM boxes_items BoxesItem WHERE BoxesItem.id = 1685 DELETE FROM boxes_items WHERE boxes_items.id = 1685 SELECT COUNT(*) AS count FROM boxes_items BoxesItem WHERE BoxesItem.id = 1686 DELETE FROM boxes_items WHERE boxes_items.id = 1686 -- snip 50 more SELECT & DELETE statements -- SELECT COUNT(*) AS count FROM boxes_items BoxesItem WHERE BoxesItem.id = 1733 DELETE FROM boxes_items WHERE boxes_items.id = 1733 DELETE FROM boxes WHERE boxes.id = 191 </code></pre> <p>This is perhaps the least efficient way to delete from these tables that I could conceive of. I mean, it could be replaced with this:</p> <pre><code>DELETE FROM boxes WHERE id = 191 DELETE FROM boxes_items WHERE box_id = 191 </code></pre> <p>Is there any reason Cake does it this way? If not, do you know of any way that I can streamline the procedure without breaking the core libraries?</p> <hr> <p>Here's the relevant bits of code:</p> <pre><code>// app/controllers/boxes_controller.php ///////////// public function delete($id = null) { if ($this->Box->del($id)) { $this->redirect(array('action'=>'index')); } } // app/models/box.php /////////////////////////////// class Boxes extends AppModel { var $hasAndBelongsToMany = array( 'Item' ); } // app/models/app_model.php ///////////////////////// class AppModel { var $actsAs = array('Containable'); var $recursive = -1; } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload