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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 [email protected]
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
