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
-~----------~----~----~----~------~----~------~--~---
