Re: parent of parent model results
Ok - so my pie in the sky advice has not been precise enough to get it working off that bat :( If you could try working on a full record retrieval to ensure everything is in place etc then work backwards to trim down the results you are getting. I am not guaranteeing that it will produce a similar single SQL query that you originally wanted (and thus a resulting dataset organised differently from what you would expect from a direct sql query) but at the minute the containable is the closest and best documented cake feature to retrieve what you want. so if you just do a $res = $this->Product->find('all', array('recursive' => 2, 'conditions'=>array ('Product.name LIKE ' => $searchstring)); debug($res); and see if it spits back the related Group Data nested withing the Collection Data. IF this isn't going to be a workable solution for you, then might be worth trying something like this. http://teknoid.wordpress.com/2008/07/17/forcing-an-sql-join-in-cakephp/ failing that, you can always use the set::extract function to process any datasets that are returned from a find call (but not always ideal or efficient) sorry couldn't be more help On Dec 16, 4:07 pm, Todd McGrath wrote: > I appreciate the tangent! Thank you, I'm learning from you. > > Unfortunately, switching contain as you mention still doesn't join to > Groups though. :( > > Here's the query it produces: > > SELECT `Product`.`id`, `Product`.`name`, `Product`.`small_image`, > `Product`.`large_image`, `Product`.`collection_id`, > `Product`.`product_type_id`, `Product`.`description`, > `Product`.`code`, `Collection`.`name`, `Collection`.`group_id` FROM > `products` AS `Product` LEFT JOIN `collections` AS `Collection` ON > (`Product`.`collection_id` = `Collection`.`id`) WHERE `Product`.`name` > LIKE 'aa' > > I posted the model code already, but here is the controller code: > > $this->set('products', $this->Product->find('all', array > ('conditions'=>array('Product.name LIKE ' => $searchstring), > 'contain' => array( > 'Collection' => array( > 'fields' => array('Collection.name', 'Collection.group_id',), > 'Group' => array( > 'fields' => array('Group.name',), > ), > ), > ) ))); > > On Dec 16, 4:58 am, Tonu Tusk wrote: > > > Ok, well just so you're clear (I know I'm not always ;) ) the cake way > > of doing things is to return the dataset of all items that are related > > to what you ask for automatically (depending on the recursion level > > set) > > so just calling product's find method would automatically pull in all > > data from other models that you had setup as related to the > > product, ... recursing down to the models that are related to the > > models that are > > related to the product. > > > The containable behaviour is there to define a subset of the > > relationships which you want to be included in the resulting dataset > > of a find call. > > This is useful in the short term to reduce the amount of unnecessary > > data that is being ripped from your db as well as memory usage etc, > > but is also good practice just to get the specific subset of data that > > you need for your task in hand as your models may change / expand in > > the future and if you > > are just doing "blind" find calls, what might not be a problem now > > with the size of data may turn into one in the future, so may as well > > do it properly to start with. > > > To go off on a slight tangent... > > If the only models you had at the moment are your Product / > > Collection / Group, then it would work to just do a > > Product->find('all') and the dataset that was returned would be close > > to what you wanted. > > However, if you had not set out a precise subset of data to retrieve > > by "contain" or any other methods you could use, then in the future > > if > > you expanded your DB to have maybe comments and reviews related to > > each product, image models related to each product as well as any > > common attributes that you might want to relate to products, all of a > > sudden the blind Product->find('all') would be pulling in a hell of a > > lot more related data. > > The fact you were asking how to tailor the equivalent of a specific > > sql call suggests that you are already taking this into consideration, > > but it is always good to > > "try" and give insight into the cake way of doing things. > > > Cake will return the datasets of the related models that you call a > > find to. You then pare down the dataset definition to what suits your > > needs. There is a subtle difference to the "defining the specifics of > > what you want cake to return" and > > "how to sculpt a current SQL call into cake language" but once your > > mindset "gets" the difference you should benefit from thinking a bit > > more cakelike and the code that ensues > > > Oh yeah, and many people are strong advocates of the fat model, > > skinny controller idealogy / methodology. > > If your 'find' calls are getting quite specific, mo
Re: parent of parent model results
I appreciate the tangent! Thank you, I'm learning from you. Unfortunately, switching contain as you mention still doesn't join to Groups though. :( Here's the query it produces: SELECT `Product`.`id`, `Product`.`name`, `Product`.`small_image`, `Product`.`large_image`, `Product`.`collection_id`, `Product`.`product_type_id`, `Product`.`description`, `Product`.`code`, `Collection`.`name`, `Collection`.`group_id` FROM `products` AS `Product` LEFT JOIN `collections` AS `Collection` ON (`Product`.`collection_id` = `Collection`.`id`) WHERE `Product`.`name` LIKE 'aa' I posted the model code already, but here is the controller code: $this->set('products', $this->Product->find('all', array ('conditions'=>array('Product.name LIKE ' => $searchstring), 'contain' => array( 'Collection' => array( 'fields' => array('Collection.name', 'Collection.group_id',), 'Group' => array( 'fields' => array('Group.name',), ), ), ) ))); On Dec 16, 4:58 am, Tonu Tusk wrote: > Ok, well just so you're clear (I know I'm not always ;) ) the cake way > of doing things is to return the dataset of all items that are related > to what you ask for automatically (depending on the recursion level > set) > so just calling product's find method would automatically pull in all > data from other models that you had setup as related to the > product, ... recursing down to the models that are related to the > models that are > related to the product. > > The containable behaviour is there to define a subset of the > relationships which you want to be included in the resulting dataset > of a find call. > This is useful in the short term to reduce the amount of unnecessary > data that is being ripped from your db as well as memory usage etc, > but is also good practice just to get the specific subset of data that > you need for your task in hand as your models may change / expand in > the future and if you > are just doing "blind" find calls, what might not be a problem now > with the size of data may turn into one in the future, so may as well > do it properly to start with. > > To go off on a slight tangent... > If the only models you had at the moment are your Product / > Collection / Group, then it would work to just do a > Product->find('all') and the dataset that was returned would be close > to what you wanted. > However, if you had not set out a precise subset of data to retrieve > by "contain" or any other methods you could use, then in the future > if > you expanded your DB to have maybe comments and reviews related to > each product, image models related to each product as well as any > common attributes that you might want to relate to products, all of a > sudden the blind Product->find('all') would be pulling in a hell of a > lot more related data. > The fact you were asking how to tailor the equivalent of a specific > sql call suggests that you are already taking this into consideration, > but it is always good to > "try" and give insight into the cake way of doing things. > > Cake will return the datasets of the related models that you call a > find to. You then pare down the dataset definition to what suits your > needs. There is a subtle difference to the "defining the specifics of > what you want cake to return" and > "how to sculpt a current SQL call into cake language" but once your > mindset "gets" the difference you should benefit from thinking a bit > more cakelike and the code that ensues > > Oh yeah, and many people are strong advocates of the fat model, > skinny controller idealogy / methodology. > If your 'find' calls are getting quite specific, move the code to a > spearate function in your model (enabling reuse as well as keeping > your controller clean) > > Tangent over. > > I misread your original post which is why the code isn't working. > > Since the Group is another level of recursive relationship from the > Product, > 'Group' variable in the contain parameter should be nested within the > Collection contain parameter (since in the > semi-abstract relationship view of the situation, the Group model is > accessible via the Collection model, not through a direct relationship > with the Product model. > > try this > > 'contain' => array( > 'Collection' => array( > 'fields' => array('Collection.name', 'collection.group_id',), > 'Group' => array( > 'fields' => array('Group.name',), > ), > ), > ) > > On Dec 15, 2:43 pm, Todd McGrath wrote: > > > Thanks. Looks like progress, but now error is: > > > Model "Product" is not associated with model "Group" [CORE/cake/libs/ > > model/behaviors/containable.php, line 317] > > > -- > > Product has one Group through Collection: > > > class Product extends AppModel { > > > var $name = 'Product'; > > var $actsAs = array('Containable'); > > var $belongsTo = array( > > 'Collection' => array('className' => 'Collection', > >
Re: parent of parent model results
Ok, well just so you're clear (I know I'm not always ;) ) the cake way of doing things is to return the dataset of all items that are related to what you ask for automatically (depending on the recursion level set) so just calling product's find method would automatically pull in all data from other models that you had setup as related to the product, ... recursing down to the models that are related to the models that are related to the product. The containable behaviour is there to define a subset of the relationships which you want to be included in the resulting dataset of a find call. This is useful in the short term to reduce the amount of unnecessary data that is being ripped from your db as well as memory usage etc, but is also good practice just to get the specific subset of data that you need for your task in hand as your models may change / expand in the future and if you are just doing "blind" find calls, what might not be a problem now with the size of data may turn into one in the future, so may as well do it properly to start with. To go off on a slight tangent... If the only models you had at the moment are your Product / Collection / Group, then it would work to just do a Product->find('all') and the dataset that was returned would be close to what you wanted. However, if you had not set out a precise subset of data to retrieve by "contain" or any other methods you could use, then in the future if you expanded your DB to have maybe comments and reviews related to each product, image models related to each product as well as any common attributes that you might want to relate to products, all of a sudden the blind Product->find('all') would be pulling in a hell of a lot more related data. The fact you were asking how to tailor the equivalent of a specific sql call suggests that you are already taking this into consideration, but it is always good to "try" and give insight into the cake way of doing things. Cake will return the datasets of the related models that you call a find to. You then pare down the dataset definition to what suits your needs. There is a subtle difference to the "defining the specifics of what you want cake to return" and "how to sculpt a current SQL call into cake language" but once your mindset "gets" the difference you should benefit from thinking a bit more cakelike and the code that ensues Oh yeah, and many people are strong advocates of the fat model, skinny controller idealogy / methodology. If your 'find' calls are getting quite specific, move the code to a spearate function in your model (enabling reuse as well as keeping your controller clean) Tangent over. I misread your original post which is why the code isn't working. Since the Group is another level of recursive relationship from the Product, 'Group' variable in the contain parameter should be nested within the Collection contain parameter (since in the semi-abstract relationship view of the situation, the Group model is accessible via the Collection model, not through a direct relationship with the Product model. try this 'contain' => array( 'Collection' => array( 'fields' => array('Collection.name', 'collection.group_id',), 'Group' => array( 'fields' => array('Group.name',), ), ), ) On Dec 15, 2:43 pm, Todd McGrath wrote: > Thanks. Looks like progress, but now error is: > > Model "Product" is not associated with model "Group" [CORE/cake/libs/ > model/behaviors/containable.php, line 317] > > -- > Product has one Group through Collection: > > class Product extends AppModel { > > var $name = 'Product'; > var $actsAs = array('Containable'); > var $belongsTo = array( > 'Collection' => array('className' => 'Collection', > 'foreignKey' > => 'collection_id', > 'conditions' > => '', > 'fields' => > '', > 'order' => '' > ), > > -- > > class Collection extends AppModel { > > var $name = 'Collection'; > var $order = 'Collection.name'; > > var $actsAs = array('Containable'); > > var $belongsTo = array( > 'Group' => array('className' => 'Group', > 'foreignKey' > => 'group_id', > 'conditions' > => '', > 'fields' => > '', > 'order' => '' > ) > ); > > Any other ideas? > > On Dec 14, 6:06 pm, Tonu Tusk wrote: > > > Hi, you can only specify the fields for the model that you are callng > > the "find" m
Re: parent of parent model results
Thanks. Looks like progress, but now error is: Model "Product" is not associated with model "Group" [CORE/cake/libs/ model/behaviors/containable.php, line 317] -- Product has one Group through Collection: class Product extends AppModel { var $name = 'Product'; var $actsAs = array('Containable'); var $belongsTo = array( 'Collection' => array('className' => 'Collection', 'foreignKey' => 'collection_id', 'conditions' => '', 'fields' => '', 'order' => '' ), -- class Collection extends AppModel { var $name = 'Collection'; var $order = 'Collection.name'; var $actsAs = array('Containable'); var $belongsTo = array( 'Group' => array('className' => 'Group', 'foreignKey' => 'group_id', 'conditions' => '', 'fields' => '', 'order' => '' ) ); Any other ideas? On Dec 14, 6:06 pm, Tonu Tusk wrote: > Hi, you can only specify the fields for the model that you are callng > the "find" method on in the 'fields' attribute in the parameter list > passed to the method call. > > You need to specify the fields you want returned in the dataset > controlled by the containable behaviour in the 'contain' variable > itself. > > e.g in your parameter array to the find call you would do > > 'contain' => array('Collection' => array('fields' => array > ('Collection.name', 'collection.group_id',)), 'Group' => array > ('fields' => array('Group.name')) > > also make sure all your model relationships are setup and also the > actsAs variable in the Product model has 'Containable' set. > > On Dec 14, 8:25 pm, Todd McGrath wrote: > > > Hello, > > > Having trouble figuring out how to construct a query such as this: > > > "SELECT products.*, groups.* FROM products Inner Join collections ON > > products.collection_id = collections.id Inner Join groups ON > > collections.group_id = groups.id WHERE products.name LIKE '%" . > > $searchstring . "'" > > > Products belongsTo Collections and Collections belongsTo Groups > > > I'm trying to use find all with conditions and fields and recursive, > > but no luck. Example: > > > $this->set('products', $this->Product->find('all', array > > ('contain' => array('Collection', 'Group'), 'conditions'=>array > > ('Product.name LIKE ' => $searchstring), 'recursive'=>3, 'fields' => > > array('Product.name','Collection.name', 'Collection.group_id', > > 'Collection.group_id','Group.name') ))); > > > but, the error is: > > Warning (512): SQL Error: 1054: Unknown column 'Group.name' in 'field > > list' [CORE/cake/libs/model/datasources/dbo_source.php, line 512] > > > How can I obtain the Group field data through Collections when > > querying Products? > > > Todd Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
Re: parent of parent model results
Hi, you can only specify the fields for the model that you are callng the "find" method on in the 'fields' attribute in the parameter list passed to the method call. You need to specify the fields you want returned in the dataset controlled by the containable behaviour in the 'contain' variable itself. e.g in your parameter array to the find call you would do 'contain' => array('Collection' => array('fields' => array ('Collection.name', 'collection.group_id',)), 'Group' => array ('fields' => array('Group.name')) also make sure all your model relationships are setup and also the actsAs variable in the Product model has 'Containable' set. On Dec 14, 8:25 pm, Todd McGrath wrote: > Hello, > > Having trouble figuring out how to construct a query such as this: > > "SELECT products.*, groups.* FROM products Inner Join collections ON > products.collection_id = collections.id Inner Join groups ON > collections.group_id = groups.id WHERE products.name LIKE '%" . > $searchstring . "'" > > Products belongsTo Collections and Collections belongsTo Groups > > I'm trying to use find all with conditions and fields and recursive, > but no luck. Example: > > $this->set('products', $this->Product->find('all', array > ('contain' => array('Collection', 'Group'), 'conditions'=>array > ('Product.name LIKE ' => $searchstring), 'recursive'=>3, 'fields' => > array('Product.name','Collection.name', 'Collection.group_id', > 'Collection.group_id','Group.name') ))); > > but, the error is: > Warning (512): SQL Error: 1054: Unknown column 'Group.name' in 'field > list' [CORE/cake/libs/model/datasources/dbo_source.php, line 512] > > How can I obtain the Group field data through Collections when > querying Products? > > Todd Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
parent of parent model results
Hello, Having trouble figuring out how to construct a query such as this: "SELECT products.*, groups.* FROM products Inner Join collections ON products.collection_id = collections.id Inner Join groups ON collections.group_id = groups.id WHERE products.name LIKE '%" . $searchstring . "'" Products belongsTo Collections and Collections belongsTo Groups I'm trying to use find all with conditions and fields and recursive, but no luck. Example: $this->set('products', $this->Product->find('all', array ('contain' => array('Collection', 'Group'), 'conditions'=>array ('Product.name LIKE ' => $searchstring), 'recursive'=>3, 'fields' => array('Product.name','Collection.name', 'Collection.group_id', 'Collection.group_id','Group.name') ))); but, the error is: Warning (512): SQL Error: 1054: Unknown column 'Group.name' in 'field list' [CORE/cake/libs/model/datasources/dbo_source.php, line 512] How can I obtain the Group field data through Collections when querying Products? Todd Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en