You're right, that was clearly the way I should have handle that.
Works like a charm.
Thanks a lot
On 18 août, 15:53, Kalt <[EMAIL PROTECTED]> wrote:
> I think you are doing it the wrong way : you don't want to order your
> buildings by flat price, you want to order them by the minimum flat
> price. Here is my solution :
>
> // controllers/buildings_controller.php
> function index() {
> // Trick to have LEFT JOIN in the query
> $this->Building->unbindModel(array('hasMany' => array('Flat')));
> $this->Building->bindModel(array('hasOne' => array('Flat')));
>
> $data = $this->Building->find('all', array(
> 'fields' => array('Building.id', 'MIN(Flat.price) AS
> flat_min_price'),
> 'group' => 'Building.id',
> 'order' => 'flat_min_price ASC'
> ));
>
> }
>
> Pixelastic a écrit :
>
> > Hello,
>
> > I'm trying to use on a find query the 'group' and 'order' parameters,
> > but the two seems not to work well together (or at least not the way I
> > would).
>
> > I have a simple structure like this :
> > Model 'Building' (id, name)
> > Model 'Flat' (id, building_id, price)
> > (and Flat belongsTo Building, obviously)
>
> > Each Building has more or less 60 flats and I would like to display on
> > BuildingController::index the list of all Buildings, ordered by flat
> > price. That means that the building with the cheaper flat will be
> > displayed first, and then the building with the second cheaper flat
> > and so on.
>
> > I tried a simple query like that :
> > $this->Flat->find(
> > 'all',
> > array(
> > 'order' => array('Flat.price' => 'ASC'),
> > 'group' => 'Flat.building_id'
> > )
> > );
> > But the GROUP BY clause is executed first, before the ORDER BY, and
> > picks results in an impredictable way. It's true that my results
> > (Flats) are ordered by price ASC, but they are not the cheaper of each
> > Building, they appear to be picked from my database in the order that
> > I added them.
>
> > I used to dealt with this problem in good old SQL by doing a query
> > like :
> > SELECT Flat.*
> > FROM (SELECT * FROM flats ORDER BY price ASC) AS Flat
> > GROUP BY Flat.building_id
> > ORDER BY Flat.price ASC
>
> > But I don't find the way to translate that query in the way the
> > Model::find() expect it (I would love to keep using the find() method
> > without custom queries in order to use beforeFind and afterFind).
>
> > Is there a way to tell the Model::find() method what to use for FROM ?
> > Or maybe someone knows a better way to achieve what I'm trying to do ?
>
>
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
