cakephp query with many tables

Question

I have a site developed in cakephp 2.0, I have some tables relationed here is an example: This is my relations:

ingredients (id,name) has many versions

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

properties (id,name,value,group_id,unit_id) has many ingredient_properties and belongs to groups,units

groups (id,name) has many properties

units (id,name) has many properties

versions (id,name,ingredient_id,active) has many ingredient_properties and belongs to ingredients.

I am in the ingredientController.php and I wanto to retrieve all this data where Version.active=1 and Version.ingredient_id=2.

This is my query:

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

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

Thanks.

[Read this page of the manual](http://book.cakephp.org/2.0/en/core-libraries/behaviors/containable.html) — Dunhamzzz, Sep 19 '12 at 09:14
Thanks I'll read the page immediately I wanto to optimize my query absolutely — Alessandro Minoccheri, Sep 19 '12 at 09:19

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

It is not the best way to use 'recursive' => 2 if you want to retrieve so much data. I believe it generates too many queries. Containable behaviour has the same drawback. The best way for me was to unbind models associations and construct table joins on the fly. You can look at an example here. But you need to know some SQL to understand what you do.

edited May 23 '17 at 12:03

Community

1
1

answered Sep 19 '12 at 22:19

bancer

7,475
7
39
58

I understand well sql, but is better to construct table joins or use containable behaviour? Consider that if I have to make joins I have to write more code rather than containable – Alessandro Minoccheri Sep 20 '12 at 06:55
If you want less code use Containable. If you want faster queries use joins. – bancer Sep 20 '12 at 08:00
It should be faster but I have not done any comparison tests. For my purposes Containable generated too many queries when by using joins I could retrieve everything I needed with a single query. – bancer Sep 20 '12 at 08:37

cakephp query with many tables

1 Answers1