开发者

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']; ?>&nbsp;</td>
    <td><?php echo $estimate['Estimate']['name']; ?>&nbsp;</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']; ?>&nbsp;</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:

  1. for this, find should be done in one query - i.e. using JOINs - and Cake won't use JOINs willingly on hasMany relationships (it does many simple SELECTs instead), which means you'd have to do them manually (http://book.cakephp.org/view/1047/Joining-tables).
  2. query with SUM would return just one row (grouped), meaning that you'd have to do un-trivial subquery to get both SUM AND your results.
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜