I have Estimates hasMany EstimateDetails hasMany Items. What I’m trying to do is get a total on my estimates Index view.<\/p>\n
The Debug of $estimates on index.ctp:<\/p>\n
Array\n(\n[0] => Array\n (\n [Estimate] => Array\n (\n [id] => 1\n [name] => City Base Cinema 10\n [employee_id] => 1\n [created] => 2011-07-12 18:08:37\n [modified] => 2011-07-12 18:08:37\n )\n\n [Employee] => Array\n (\n [id] => 1\n [first_name] => Edward\n [last_name] => Ramon\n [phone] => 2106959672\n [hourly_pay] => 0.00\n [position_id] => 2\n [created] => 2011-07-12 17:56:42\n [modified] => 2011-07-12 17:56:42\n [fullname] => Edward Ramon\n [Position] => Array\n (\n [id] => 2\n [name] => Estimator\n )\n\n [Estimate] => Array\n (\n [0] => Array\n (\n [id] => 1\n [name] => City Base Cinema 10\n [employee_id] => 1\n [created] => 2011-07-12 18:08:37\n [modified] => 2011-07-12 18:08:37\n )\n\n )\n\n [Job] => Array\n (\n )\n\n )\n\n [EstimateDetail] => Array\n (\n [0] => Array\n (\n [id] => 1\n [estimate_id] => 1\n [qty] => 10\n [item_id] => 1\n [feet] => 10\n [inches] => 2\n [adjustment] => -0.20\n [Estimate] => Array\n (\n [id] => 1\n [name] => City Base Cinema 10\n [employee_id] => 1\n [created] => 2011-07-12 18:08:37\n [modified] => 2011-07-12 18:08:37\n )\n\n [Item] => Array\n (\n [id] => 1\n [type] => W\n [eje] => W\n [description] => 44x335\n [price unit] => perpound\n [weight] => 335\n [price] => 0.80\n [fulldesc] => W44x335\n )\n\n )\n\n [1] => Array\n (\n [id] => 3\n [estimate_id] => 1\n [qty] => 3\n [item_id] => 1\n [feet] => 4\n [inches] => 5\n [adjustment] => 0.00\n [Estimate] => Array\n (\n [id] => 1\n [name] => City Base Cinema 10\n [employee_id] => 1\n [created] => 2011-07-12 18:08:37\n [modified] => 2011-07-12 18:08:37\n )\n\n [Item] => Array\n (\n [id] => 1\n [type] => W\n [eje] => W\n [description] => 44x335\n [price unit] => perpound\n [weight] => 335\n [price] => 0.80\n [fulldesc] => W44x335\n )\n\n )\n\n [2] => Array\n (\n [id] => 4\n [estimate_id] => 1\n [qty] => 10\n [item_id] => 1\n [feet] => 10\n [inches] => 10\n [adjustment] => 0.00\n [Estimate] => Array\n (\n [id] => 1\n [name] => City Base Cinema 10\n [employee_id] => 1\n [created] => 2011-07-12 18:08:37\n [modified] => 2011-07-12 18:08:37\n )\n\n [Item] => Array\n (\n [id] => 1\n [type] => W\n [eje] => W\n [description] => 44x335\n [price unit] => perpound\n [weight] => 335\n [price] => 0.80\n [fulldesc] => W44x335\n )\n\n )\n\n [3] => Array\n (\n [id] => 5\n [estimate_id] => 1\n [qty] => 10\n [item_id] => 3\n [feet] => 10\n [inches] => 10\n [adjustment] => 0.00\n [Estimate] => Array\n (\n [id] => 1\n [name] => City Base Cinema 10\n [employee_id] => 1\n [created] => 2011-07-12 18:08:37\n [modified] => 2011-07-12 18:08:37\n )\n\n [Item] => Array\n (\n [id] => 3\n [type] => W\n [eje] => W\n [description] => 44x290\n [price unit] => perpound\n [weight] => 290\n [price] => 0.80\n [fulldesc] => W44x290\n )\n\n )\n\n [4] => Array\n (\n [id] => 6\n [estimate_id] => 1\n [qty] => 10\n [item_id] => 6\n [feet] => 10\n [inches] => 2\n [adjustment] => 0.00\n [Estimate] => Array\n (\n [id] => 1\n [name] => City Base Cinema 10\n [employee_id] => 1\n [created] => 2011-07-12 18:08:37\n [modified] => 2011-07-12 18:08:37\n )\n\n [Item] => Array\n (\n [id] => 6\n [type] => PL\n [eje] => PL\n [description] => 4X3\n [price unit] => ea\n [weight] => 1\n [price] => 0.30\n [fulldesc] => PL4X3\n )\n\n )\n\n )\n\n [Job] => Array\n (\n [0] => Array\n (\n [id] => 1\n [job_number] => 2353\n [name] => City Base Cinema 10\n [company_id] => 1\n [estimate_id] => 1\n [contract_amount] => 253000\n [employee_id] => 2\n [location_id] => 1\n [created] => 2011-07-12 18:17:59\n [modified] => 2011-07-16 12:17:55\n [Company] => Array\n (\n [id] => 1\n [name] => Search Construction\n [address] => 123 street\n [city] => San Antonio\n [state] => TX\n [zip] => 78023\n [company_type_id] => 1\n [created] => 2011-07-12 18:16:37\n [modified] => 2011-07-12 18:16:37\n )\n\n [Estimate] => Array\n (\n [id] => 1\n [name] => City Base Cinema 10\n [employee_id] => 1\n [created] => 2011-07-12 18:08:37\n [modified] => 2011-07-12 18:08:37\n )\n\n [Employee] => Array\n (\n [id] => 2\n [first_name] => Adam\n [last_name] => Morales\n [phone] => \n [hourly_pay] => 20.00\n [position_id] => 1\n [created] => 2011-07-16 12:16:49\n [modified] => 2011-07-16 12:16:49\n [fullname] => Adam Morales\n )\n\n [Location] => Array\n (\n [id] => 1\n [address] => 456 street\n [city] => Helotes\n [state] => TX\n [zip] => 78023\n [location_type_id] => 1\n [full_local] => 456 street Helotes, TX 78023\n )\n\n )\n\n )\n\n )\n\n)\n<\/code><\/pre>\nI tried setting a variableField on the Estimate Model:<\/p>\n
var $virtualFields = array('total' => 'SUM(EstimateDetail.qty*EstimateDetail.Item.weight*EstimateDetail.Item.price)');\n<\/code><\/pre>\nMy Index.ctp:<\/p>\n
\n<\/th>\n <\/th>\n <\/th>\n <\/th>\n <\/th>\n <\/th>\n<\/tr>\n\u00a0<\/p>\n
\u00a0<\/td>\n <\/td>\n \u00a0<\/td>\n <\/td>\nand I get an error.<\/p>\n
The error I get is:<\/p>\n
Warning (2): Invalid argument supplied for foreach() [APP\/views\/estimates\/index.ctp, line 15]\n<\/code><\/pre>\nand the Sql:<\/p>\n
1054: Unknown column 'EstimateDetail.qty' in 'field list\n<\/code><\/pre>\nPlease Help.<\/p>\n
\n- \n
Short answer would be – don’t try to do what you’re doing with SQL\/virtualFields<\/code> (if you want, I can elaborate on details why).<\/p>\nJust iterate over your results via foreach($estimates)<\/code> and calculate sum yourself in PHP.<\/p>\nUPDATE<\/strong>: You can’t (easily) use aggregate (SUM<\/code>) in find()<\/code> in your example because:<\/p>\n\n- for this, find should be done in one query – i.e. using
JOIN<\/code>s – and Cake won’t use JOIN<\/code>s willingly on hasMany relationships (it does many simple SELECT<\/code>s instead), which means you’d have to do them manually (http:\/\/book.cakephp.org\/view\/1047\/Joining-tables).<\/li>\n- query with
SUM<\/code> would return just one row (grouped), meaning that you’d have to do un-trivial subquery to get both SUM<\/code> AND your results.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\nOriginally posted 2013-11-09 22:48:01. <\/small><\/p>","protected":false},"excerpt":{"rendered":"
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] => […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-831","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/831","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=831"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/831\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=831"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=831"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=831"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}