cakePHP running the same query multiple times

Hi All

I will try to keep this high-level to start with, I am convinced it is a problem with how my models are related to each other.

I am developing in cakePHP and all data is being returned fine but I am having performance issues. Essesntially I have a documents category table and I display the relevant documents underneath. Upon closer inspection the query for each document is running for each category. I have twenty-six categories and if I have two documents to display the following is being run twenty-six times:

SELECT `document_files`.`document_files_id`, `document_files`.`document_files_document_id`, `document_files`.`document_files_date`, `document_files`.`document_files_date_uploaded`, `document_files`.`document_files_author`, WHERE `document_files`.`document_files_document_id` = 466

SELECT `document_files`.`document_files_id`, `document_files`.`document_files_document_id`, `document_files`.`document_files_date`, `document_files`.`document_files_date_uploaded`, `document_files`.`document_files_author`, WHERE `document_files`.`document_files_document_id` = 510

Surely it should only run once, any help would be appreciated. I have 2000+ queries running per page and the page load is unbearable.
save9Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
xardas2010Connect With a Mentor Commented:
You can use containable on any model, but you must be careful to get only your data and to reach it correctly. You can also select only the fields you need, and if you go throw other models that you don't need, select only the ID so the query gets less bigger. Something like:

find('all', array('fields' => array('id', 'name'),
                        'contain' => array('Model1' => array('fields' => array('id'),
                                                                                 'Model2' => array('fields' => array('id'),
                                                                                                             'Model3' => array('fields' => array('id', ''name)))))));

You can read more about containable here: http://book.cakephp.org/view/474/Containable

If you want to optimize, maybe first you should change the database structure a bit. For me it doesn't seem good, to go throw 4 models to connect a document with his category.
0
 
xardas2010Connect With a Mentor Commented:
Are you using recursive or containable to retrieve your data ?
If I got it right, you want to select all your categories and their books. In this case, add the containable behavior to your "Category" model and try it like this:

$select = $this->Category->find('all', array('contain' => array('Book')));
0
 
save9Author Commented:
Hi xardas

This is where I am falling down, I have inherited the code and is much more complicated than described above.

The duplicate code is kicked off in a HABTM relationship but tit goes through four models before it reaches it. I have traced it back and the findAll isn't a standard one:

$this->page->document_categories->findAllBydocument_categories_parent_id($doc_cat['document_categories_id'])

Can I add the contain attribute to this?

0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
save9Author Commented:
Just to explain further:

Starts by getting the document_categories related to the page (see above).

The docuemnts_categories has an HABTM relationship with the documents model

The docuements model ahs a hasOne relationship with a document_file model - this is the query running for every docuemnt category.

0
 
save9Author Commented:
Hi Xardas

Unfortunately I cannot change the DB structure.

I think what you are suggesting is correct but struggling to get it working. Will keep you updated.
0
 
save9Author Commented:
I am unable to implement the solution due to structural limitations.
0
All Courses

From novice to tech pro — start learning today.