CiviCRM Community Forums (archive)

Oh yeah... Debugging. That might be a good idea to have turned on 

Ok, so here is the back trace (I have to step out but I'll be back shortly):


backTrace

#0 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/CRM/Core/Error.php(149): CRM_Core_Error::backtrace()
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: unknown error", -1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 AS SELECT SQL_CAL...")
#4 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 AS SELECT SQL_CAL...")
#5 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -1, NULL, NULL, "CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 AS SELECT SQL_CAL...", "DB_Error", TRUE)
#6 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-1, NULL, NULL, NULL, "1060 ** Duplicate column name '1'")
#7 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 AS SELECT SQL_CAL...")
#9 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/packages/DB/DataObject.php(2421): DB_common->query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 AS SELECT SQL_CAL...")
#10 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/packages/DB/DataObject.php(1613): DB_DataObject->_query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 AS SELECT SQL_CAL...")
#11 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/CRM/Core/DAO.php(160): DB_DataObject->query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 AS SELECT SQL_CAL...")
#12 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/CRM/Core/DAO.php(907): CRM_Core_DAO->query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 AS SELECT SQL_CAL...", TRUE)
#13 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/default/files/civicrm/extensions/ca.lunahost.civitax/CRM/Report/Form/Contribute/Detail.php(685): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 AS SELECT SQL_CAL...")
#14 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/CRM/Core/Form.php(261): CRM_Report_Form_Contribute_Detail->postProcess()
#15 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Submit.php(73): CRM_Core_Form->mainProcess()
#16 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Submit->perform(Object(CRM_Report_Form_Contribute_Detail), "submit")
#17 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Report_Form_Contribute_Detail), "submit")
#18 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/CRM/Core/Controller.php(345): HTML_QuickForm_Page->handle("submit")
#19 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(117): CRM_Core_Controller->run()
#20 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/CRM/Report/Page/Instance.php(103): CRM_Utils_Wrapper->run("CRM_Report_Form_Contribute_Detail", NULL, NULL)
#21 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/CRM/Core/Invoke.php(323): CRM_Report_Page_Instance->run((Array:4), NULL)
#22 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/CRM/Core/Invoke.php(72): CRM_Core_Invoke::runItem((Array:14))
#23 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#24 /home/auragold/public_html/civitax.lunadesignhosting.com/sites/all/modules/civicrm/drupal/civicrm.module(456): CRM_Core_Invoke::invoke((Array:4))
#25 [internal function](): civicrm_invoke("report", "instance", "7")
#26 /home/auragold/public_html/civitax.lunadesignhosting.com/includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:3))
#27 /home/auragold/public_html/civitax.lunadesignhosting.com/index.php(21): menu_execute_active_handler()
#28 {main}



 

Thanks. Now we're onto something! I can do a workaround and force it to work or I can do it right!

Yes the problem was that I was returning a 1 into the select query for every custom tax field I select. That is the most valuable piece of info for the whole exercise for me... I didn't realize that was going on 

The workaround is to just return some unique number that will get stuffed in the query (and ignored) and the rest of my code works as I had planned... My LEFT JOIN is fine where it is and I can force my dynamic sub queries into the query right before the FROM statement begins.

But that just doesn't feel right 

Now (thanks to all of your help and prodding) I have more of a clue about using selectClause properly. My DAO has a field element called 'where' that I can use to return my sub query, which is the right way to do it.

The only problem with that approach is that it still crashes when I run it that way but I know the reason. The error now is Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS'

My Subquery is
(SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing....
For some reason when I retunr it from selectClause, it is being rewritten to:
(SELECT SQL_CALC_FOUND_ROWS civi_tax_invoicing.tax_charged FROM civi_tax_invoicing...
Do you know how I can escape it so that it doesn't add the SQL_CALC_FOUND_ROWS clause?

The complete Query returned is:
SELECT SQL_CALC_FOUND_ROWS
contact_civireport.sort_name as civicrm_contact_sort_name,
contact_civireport.id as civicrm_contact_id,
email_civireport.email as civicrm_email_email,
contacthonor.id as civicrm_contact_honor_id_honor,
_invoicing_civireport.pre_tax as civi_tax_invoicing_pre_tax,
(SELECT SQL_CALC_FOUND_ROWS civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE contribution_civireport.invoice_id = civi_tax_invoicing.invoice_id COLLATE utf8_unicode_ci AND civi_tax_invoicing.tax_id = 3) AS civi_tax_invoicing_tax_hst,
sum(_invoicing_civireport.tax_charged) as civi_tax_invoicing_tax_charged_sum, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id,
contribution_civireport.currency as civicrm_contribution_currency,
contribution_civireport.receive_date as civicrm_contribution_receive_date,
contribution_civireport.total_amount as civicrm_contribution_total_amount,
address_civireport.country_id as civicrm_address_country_id

FROM civicrm_contact contact_civireport

INNER JOIN civicrm_contribution contribution_civireport ON contact_civireport.id = contribution_civireport.contact_id AND contribution_civireport.is_test = 0

LEFT JOIN civicrm_address address_civireport ON contact_civireport.id = address_civireport.contact_id AND address_civireport.is_primary = 1

LEFT JOIN civicrm_email email_civireport ON contact_civireport.id = email_civireport.contact_id AND email_civireport.is_primary = 1

LEFT JOIN civicrm_contact contacthonor ON contacthonor.id = contribution_civireport.honor_contact_id

LEFT JOIN civi_tax_invoicing _invoicing_civireport ON contribution_civireport.invoice_id = _invoicing_civireport.invoice_id COLLATE utf8_unicode_ci

WHERE (1)

AND ( contribution_civireport.receive_date >= 20140301000000 )
AND ( contribution_civireport.receive_date <= 20140331235959 )
AND contact_civireport.is_deleted = 0

GROUP BY contact_civireport.id, contribution_civireport.id

ORDER BY contact_civireport.sort_name ASC LIMIT 0, 50

If I remove SQL_CALC_FOUND_ROWS and run it on MySQL Workbench it runs just fine.

This is quite awesome 

Thanks for all the help!