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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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')));
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:


Can I add the contain attribute to this?

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.

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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:

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
save9Author Commented:
I am unable to implement the solution due to structural limitations.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.