Cakephp query with many tables

Question

I have a website developed in cakephp 2.0, I have some tables that relate to this example: This is my relationship:

Ingredients (id, name) have many versions

componentient_properties (id, property_id, version_id) belongs to properties, versions

properties (id, name, value, group_id, unit_id) have many componentent_properties and belong to groups, units

groups (id, name) has many properties

units (id, name) has many properties

versions (id, name, componentent_id, active) contains many ingredients and ingredients.

I am in componentController.php component and I want to get all this data where Version.active=1 and Version.ingredient_id=2 .

This is my request:

 $this->set( 'ingredient', $this->Ingredient->Version->find('all', array( 'recursive' => 2, 'conditions' => array( 'Version.active' => 1, 'Version.ingredient_id' => 2 ) )) ); 

I have many, many queries like this, and I want to know if recursive 2 is the best way to get all the table data that I explained, or is there a better way the fastest (in terms of query speed, not for implementation). I hope someone can help me optimize my code because this query works, but I don't know if it is the best way to retrieve data from many related tables.

Thanks.

Answer 1

This is not the best way to use 'recursive' => 2 if you want to extract so much data. I believe this generates too many requests. Resistant behavior has the same drawback. The best way for me was to disable model associations and build join tables on the fly. You can see an example here . But you need to know some SQL to understand what you are doing.