This insight is truly helpful, however, my associations are a little
different.
I have an Offer which belongs to a resort. A Resort belongs to both a
State and a Destination, and hasMany Offers. Both States and
Destinations hasMany Resorts. There is also a secondary association
between Resorts and States where a Resort can be associated with many
states.
What I am trying to do is a findAll on my Specials, but have them
grouped by Destination.
As I have things working right now, the findAll gives me back all of the
data that I require; I just don't know how to do the group by in the
query. I can always do the grouping after the fact in PHP, but I'd
rather have this done by the database, as it's the most logical place to
do it.
Models are as follows:
class Offer extends AppModel
{
var $name = 'Offer';
var $recursive = 2;
var $belongsTo = array('Resort' => array('className' => 'Resort',
'conditions' => '',
'order' => '',
'foreignKey' => 'resort_id'
));
}
class Resort extends AppModel
{
var $name = 'Resort';
var $belongsTo = array('State' => array('className' => 'State',
'conditions' => '',
'order' => '',
'foreignKey' => 'state_id'
),
'Destination' => array('className' => 'Destination',
'conditions' => '',
'order' => '',
'foreignKey' =>'destination_id'
));
var $hasMany = array('Offer' =>
array('className' => 'Offer',
'conditions' => 'Offer.isActive = 1',
'order' => 'Offer.created DESC',
'limit' => '',
'foreignKey' => 'resort_id',
'dependent' => true,
'exclusive' => false,
'finderQuery' => ''
)
);
var $hasAndBelongsToMany = array('States' => array('className' =>
'State',
'joinTable' => 'resorts_states',
'foreignKey' => 'resort_id',
'associationForeignKey'=> 'state_id',
'conditions' => '',
'order' => '',
'limit' => '',
'unique' => true,
'finderQuery' => '',
'deleteQuery' => '',
));
-Erich-
AD7six wrote:
>
> On Dec 9, 1:32 am, "Mariano Iglesias" <[EMAIL PROTECTED]>
> wrote:
>> You could use a custom query:
>>
>> $this->Model->findAll('GROUP BY field', null, null);
>>
>> Or you could also use this method:
>>
>> http://groups-beta.google.com/group/cake-php/browse_thread/thread/20f7b0dda
>
> Hi All,
>
> After an hour or two bugging Nate (muchisimus thankius) and trying to
> figure out why in one circ I couldn't get this to work, here is some
> distilled info:
>
> If you want to include some aggreagate info from a hasMany associated
> model, first of all use bind Model to create a dummy hasOne association
> so that the model is part of the query.
>
> Bear in mind that for a simple select (SELECT blah FROM tables WHERE $x
> ORDER BY $y LIMIT $z), $x is the conditions and depends on what you
> pass (obvisously):
>
> where you pass an array constraint you will get:
> (First array element) AND (second array element). As such the approach
> mentioned in the link may not work.
>
> where you pass a string constraint:
> "Model.field = value" will give you "SELECT blah FROM tables WHERE
> Model.field = value ORDER BY.."
> Therefore
> "GROUP BY x.y" will give you "SELECT blah FROM tables WHERE GROUP BY
> x.y ORDER BY.." Which isn't syntactically correct
> BUT
> "1=1 GROUP BY x.y" will give you "SELECT blah FROM tables WHERE 1=1
> GROUP BY x.y ORDER BY.." Which is correct
>
> Be aware that if you have a hasOne or belongsTo association that has a
> condition, you will need to 'integrate' this condition into your find
> conditions, and remove it from the association (with bindModel) to
> avoid getting "WHERE (String Constraint including GROUP BY) AND
> (association condition) ORDER BY.."
>
> Here's a working example:
> $this->Blog->bindModel(array('hasOne'=>array('Dummy'=>array('className'=>'Comment','foreignKey'=>'foreign_id'))));
> $this->data = $this->Blog->FindAll('`Dummy`.`class`=\'blog\' GROUP BY
> `Blog`.`id`',array('id','title','status','created','COUNT(`Dummy`.`id`)
> AS comment_count'),$order, $limit, $page,0);
>
> HTH,
>
> AD7six
> Please note:
> The manual/bakery is a good place to start any quest for info.
> The cake search (at the time of writing) erroneously reports less/no
> results for the google group.
> The wiki may contain incorrect info - read at your own risk (it's
> mainly user submitted) :) You may get your answer quicker by asking on
> the IRC Channel (you can access it with just a browser
> here:http://irc.cakephp.org).
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Cake PHP" 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
-~----------~----~----~----~------~----~------~--~---
