How to get total price from estimate to show up in index.ctp using CakePHP
I have Estimates hasMany EstimateDetails hasMany Items. What I'm trying to do is get a total on my 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
(
开发者_Go百科 [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 a variableField on the 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 as $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 you sure you want to delete # %s?', true), $estimate['Estimate']['id'])); ?>
</td>
</tr>
and I get an error.
The error I get is:
Warning (2): Invalid argument supplied for foreach() [APP/views/estimates/index.ctp, line 15]
and the Sql:
1054: Unknown column 'EstimateDetail.qty' in 'field list
Please Help.
Short answer would be - don't try to do what you're doing with SQL/virtualFields (if you want, I can elaborate on details why).
Just iterate over your results via foreach($estimates) and calculate sum yourself in PHP.
UPDATE: You can't (easily) use aggregate (SUM) in find() in your example because:
- for this, find should be done in one query - i.e. using
JOINs - and Cake won't useJOINs willingly on hasMany relationships (it does many simpleSELECTs instead), which means you'd have to do them manually (http://book.cakephp.org/view/1047/Joining-tables). - query with
SUMwould return just one row (grouped), meaning that you'd have to do un-trivial subquery to get bothSUMAND your results.
加载中,请稍侯......
精彩评论