view - How to get total price from estimate to show up in index.ctp using CakePHP -
i have estimates hasmany estimatedetails hasmany items. i'm trying total on estimates index view.
the debug of $estimates on index.ctp:
array ( [0] => array ( [estimate] => array ( [id] => 1 [name] => city base cinema 10 [employee_id] => 1 [created] => 2011-07-12 18:08:37 [modified] => 2011-07-12 18:08:37 ) [employee] => array ( [id] => 1 [first_name] => edward [last_name] => ramon [phone] => 2106959672 [hourly_pay] => 0.00 [position_id] => 2 [created] => 2011-07-12 17:56:42 [modified] => 2011-07-12 17:56:42 [fullname] => edward ramon [position] => array ( [id] => 2 [name] => estimator ) [estimate] => array ( [0] => array ( [id] => 1 [name] => city base cinema 10 [employee_id] => 1 [created] => 2011-07-12 18:08:37 [modified] => 2011-07-12 18:08:37 ) ) [job] => array ( ) ) [estimatedetail] => array ( [0] => array ( [id] => 1 [estimate_id] => 1 [qty] => 10 [item_id] => 1 [feet] => 10 [inches] => 2 [adjustment] => -0.20 [estimate] => array ( [id] => 1 [name] => city base cinema 10 [employee_id] => 1 [created] => 2011-07-12 18:08:37 [modified] => 2011-07-12 18:08:37 ) [item] => array ( [id] => 1 [type] => w [eje] => w [description] => 44x335 [price unit] => perpound [weight] => 335 [price] => 0.80 [fulldesc] => w44x335 ) ) [1] => array ( [id] => 3 [estimate_id] => 1 [qty] => 3 [item_id] => 1 [feet] => 4 [inches] => 5 [adjustment] => 0.00 [estimate] => array ( [id] => 1 [name] => city base cinema 10 [employee_id] => 1 [created] => 2011-07-12 18:08:37 [modified] => 2011-07-12 18:08:37 ) [item] => array ( [id] => 1 [type] => w [eje] => w [description] => 44x335 [price unit] => perpound [weight] => 335 [price] => 0.80 [fulldesc] => w44x335 ) ) [2] => array ( [id] => 4 [estimate_id] => 1 [qty] => 10 [item_id] => 1 [feet] => 10 [inches] => 10 [adjustment] => 0.00 [estimate] => array ( [id] => 1 [name] => city base cinema 10 [employee_id] => 1 [created] => 2011-07-12 18:08:37 [modified] => 2011-07-12 18:08:37 ) [item] => array ( [id] => 1 [type] => w [eje] => w [description] => 44x335 [price unit] => perpound [weight] => 335 [price] => 0.80 [fulldesc] => w44x335 ) ) [3] => array ( [id] => 5 [estimate_id] => 1 [qty] => 10 [item_id] => 3 [feet] => 10 [inches] => 10 [adjustment] => 0.00 [estimate] => array ( [id] => 1 [name] => city base cinema 10 [employee_id] => 1 [created] => 2011-07-12 18:08:37 [modified] => 2011-07-12 18:08:37 ) [item] => array ( [id] => 3 [type] => w [eje] => w [description] => 44x290 [price unit] => perpound [weight] => 290 [price] => 0.80 [fulldesc] => w44x290 ) ) [4] => array ( [id] => 6 [estimate_id] => 1 [qty] => 10 [item_id] => 6 [feet] => 10 [inches] => 2 [adjustment] => 0.00 [estimate] => array ( [id] => 1 [name] => city base cinema 10 [employee_id] => 1 [created] => 2011-07-12 18:08:37 [modified] => 2011-07-12 18:08:37 ) [item] => array ( [id] => 6 [type] => pl [eje] => pl [description] => 4x3 [price unit] => ea [weight] => 1 [price] => 0.30 [fulldesc] => pl4x3 ) ) ) [job] => array ( [0] => array ( [id] => 1 [job_number] => 2353 [name] => city base cinema 10 [company_id] => 1 [estimate_id] => 1 [contract_amount] => 253000 [employee_id] => 2 [location_id] => 1 [created] => 2011-07-12 18:17:59 [modified] => 2011-07-16 12:17:55 [company] => array ( [id] => 1 [name] => search construction [address] => 123 street [city] => san antonio [state] => tx [zip] => 78023 [company_type_id] => 1 [created] => 2011-07-12 18:16:37 [modified] => 2011-07-12 18:16:37 ) [estimate] => array ( [id] => 1 [name] => city base cinema 10 [employee_id] => 1 [created] => 2011-07-12 18:08:37 [modified] => 2011-07-12 18:08:37 ) [employee] => array ( [id] => 2 [first_name] => adam [last_name] => morales [phone] => [hourly_pay] => 20.00 [position_id] => 1 [created] => 2011-07-16 12:16:49 [modified] => 2011-07-16 12:16:49 [fullname] => adam morales ) [location] => array ( [id] => 1 [address] => 456 street [city] => helotes [state] => tx [zip] => 78023 [location_type_id] => 1 [full_local] => 456 street helotes, tx 78023 ) ) ) ) ) i tried setting variablefield on estimate model:
var $virtualfields = array('total' => 'sum(estimatedetail.qty*estimatedetail.item.weight*estimatedetail.item.price)'); my index.ctp:
<tr> <th><?php echo $this->paginator->sort('id');?></th> <th><?php echo $this->paginator->sort('name');?></th> <th><?php echo $this->paginator->sort('employee');?></th> <th><?php echo $this->paginator->sort('created');?></th> <th><?php echo $this->paginator->sort('modified');?></th> <th class="actions"><?php __('actions');?></th> </tr> <?php $i = 0; foreach ($estimates $estimate): $class = null; if ($i++ % 2 == 0) { $class = ' class="altrow"'; } ?> <tr<?php echo $class;?>> <td><?php echo $estimate['estimate']['id']; ?> </td> <td><?php echo $estimate['estimate']['name']; ?> </td> <td> <?php echo $this->html->link($estimate['employee']['fullname'], array('controller' => 'employees', 'action' => 'view', $estimate['employee']['id'])); ?> </td> <td><?php echo $estimate['estimate']['created']; ?> </td> <td class="actions"> <?php echo $this->html->link(__('view', true), array('action' => 'view', $estimate['estimate']['id'])); ?> <?php echo $this->html->link(__('edit', true), array('action' => 'edit', $estimate['estimate']['id'])); ?> <?php echo $this->html->link(__('delete', true), array('action' => 'delete', $estimate['estimate']['id']), null, sprintf(__('are sure want delete # %s?', true), $estimate['estimate']['id'])); ?> </td> </tr>
and error.
the error is:
warning (2): invalid argument supplied foreach() [app/views/estimates/index.ctp, line 15] and sql:
1054: unknown column 'estimatedetail.qty' in 'field list please help.
short answer - don't try you're doing sql/virtualfields (if want, can elaborate on details why).
just iterate on results via foreach($estimates) , calculate sum in php.
update: can't (easily) use aggregate (sum) in find() in example because:
- for this, find should done in 1 query - i.e. using
joins - , cake won't usejoins willingly on hasmany relationships (it many simpleselects instead), means you'd have them manually (http://book.cakephp.org/view/1047/joining-tables). - query
sumreturn 1 row (grouped), meaning you'd have un-trivial subquery bothsum, results.
Comments
Post a Comment