How to get total price from estimate to show up in index.ctp using CakePHP-Collection of common programming errors
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
(
[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:
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) infind()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.
- for this, find should be done in one query – i.e. using
Originally posted 2013-11-09 22:48:01.