cakephp make same query recursive

Question

I have a site develop in cakephp 2.0. I have a table "Product" related to property with hasMany, and HABTM to Product. A product can contain many product and that product can contain many product. I can have multiple level of product. To make this relation I have used this model (explained into this question: cakephp HABTM same model):

class Product extends AppModel {
    public $name = 'Product';
    public $useTable = 'products';
    public $belongsTo = 'User';
    public $actsAs = array('Containable');

        public $hasAndBelongsToMany = array(
        'Ingredient' => array(
            'className' => 'Product',
            'joinTable' => 'ingredients_products',
            'foreignKey' => 'product_id',
            'associationForeignKey' => 'ingredient_id', 
            'unique' => true
        )
    );

}

I know how to retrieve product example: I have a product that contain other product I make a find with condition and I retrieve all data with their property like this:

$this->set('product',$this->Product->find('all', array(
                    'contain' => array(
                            'Property',
                            'IngredientProduct'
                        ),
                    'conditions' => array('id' => $id)  
                )));

(IS ONLY AN EXAMPLE THE QUERY I DON?T KNOW IF IS GOOD DEPENDS OF MANY THINGS IS ONLY AN EXAMPLE)

But if I find 3/4 product inside my product and if findedproduct contain other product how can I do that? Because I don't know how many "levels" I have I can have 1 level or 6 level of product. A product contain many products, this finded products contain other products ecc.. How can I do that and retrieve for each product its property? It's a recursion query but the problem is that I don't know how many level I have. Some ideas? Thanks

score 1 · Accepted Answer · edited May 23 '17 at 11:56

1

Containable does not traverse so deep. I would advice to unbind your associations and join tables manually. Take a look here to see an example and at this cookbook chapter (you need to know SQL in your case). Or you can use TreeBehavior as Dave suggested.

edited May 23 '17 at 11:56

Community

1
1

answered Oct 09 '12 at 12:20

bancer

7,475
7
39
58

But I have to make a nest join right? a join inside a join inside a join until a product doesn't have a product inside it right? But my problem isn't how to make the query but how to make a recursive query to retrieve all product until a product doens't have a product inside it – Alessandro Minoccheri Oct 09 '12 at 12:23
As I wrote you need to know SQL to perform such complex queries. Take a look at this question about recursive queries http://stackoverflow.com/questions/10646833/using-mysql-query-to-traverse-rows-to-make-a-recursive-tree So, I guess, that is not an option for you, so use TreeBehavior. Maybe it would generate more than one query but it is easy to use. – bancer Oct 09 '12 at 12:32
I know SQL and I don't want to use the easy way but the best and perform way because I have a lot of records to retrieve. I try with TreeBehavior and the join. I don't know if TreeBehavior is my way – Alessandro Minoccheri Oct 09 '12 at 12:40
There is no build-in option for recursive queries in CakePHP if that is what you wanted to ask. There is topic on complex queries in the cookbook - http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#complex-find-conditions. So, if you decide to go this way read it. You can also construct query "by hands" and pass it to your model: `$this->Product->query('YOUR COMPLEX QUERY');` – bancer Oct 09 '12 at 12:46
I'm thinking to try a thing like that: a query to retrieve the first level of product, for each product a make another query ad add it to my previous array into a loop for every level. Is complex, but this evening I try to make the join level – Alessandro Minoccheri Oct 09 '12 at 12:48
In that case better use TreeBehavior. It will construct these queries for you and will save your time (if you can modify your database schema, of course). – bancer Oct 09 '12 at 12:51

score 0 · Answer 2 · answered Oct 09 '12 at 08:27

0

Maybe you could/should go with something like - all products are just in "products" table, then you have a tree table that keeps the entire product hierarchy?

answered Oct 09 '12 at 08:27

Dave

28,833
23
113
183

I don't understand totally.. can you make an example to clear my problem? – Alessandro Minoccheri Oct 09 '12 at 08:28

cakephp make same query recursive

2 Answers2