Using any type of sql function is easy in cake.
Your example would be as simple as:
$data = $this->Cake->find('all', array(
'fields' => array('price', 'name', '(2*protein+LOG(fibre)-10*salt-
sugar-fat) AS health'),
'order' => 'health',
'limit' => 10
));
The one 'gotcha' is that the result won't be where you might expect in
the returned data. Calculated fields have no associated table, so as
far as cake can see; they belong to no model. So your returned data
array would look like this:
[0] => array(
'Cake' => array('price' => 123, 'name' => 'Grigri\'s homemade lemon
curd cheesecake'),
[0] => array('health' => 1.8e-308)
),
[1] => array(
'Cake' => ...
[0] => ...
),
Note that the calculated field "health" is indexed under [0], not
'Cake' or some other joined model.
There are a number of behaviors floating around to sort this out, but
it's usually easier just to work with the data in-place. If you have a
second calculated field it will be indexed under [0] too.
Note that you can cheat on some databases (postgres for example) by
doing '(...) AS Model__field' (double-underscore), and it will get
indexed with the model. Not sure if this a good idea though.
hth
grigri
On Apr 25, 2:26 pm, Mark Lawton <[EMAIL PROTECTED]> wrote:
> Hi Everyone,
>
> I would like to switch to using Cake, but looking
> athttp://manual.cakephp.org/view/66/models, I don't see an obvious way
> to ask for a calculated ordered list from a database - and these are
> indispensable to our website. Suppose, for example, one wanted the 10
> healthiest cakes: the SQL query might look like this:
>
> select price, name, 2 * protein + log(fibre) - 10 * salt - sugar - fat
> as health order by health limit 10
>
> Can somebody tell me how we would be able to do such lists in Cake,
> please?
>
> btw - although the above SQL query is obviously contrived, we really
> do use mathematical functions like log() in making our ordered lists.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
