I'm trying to add Notes field to the advanced Contribution search so that I can do wildcard search on the basis of notes but the DAO throwing error: "unknown column", as it's not able to locate civicrm_note table.
Here's my extension file
function MyExtension_civicrm_buildForm($formName, &$form) {
if ($formName == 'CRM_Contribute_Form_Search' || $formName == 'CRM_Contact_Form_Search_Advanced') {
$form->add('textarea',
'contribution_note',
ts('Note'),
['rows' => 4, 'cols' => 40],
FALSE,
array('class' => 'crm-form-textarea')
);
}
}
Custom template file
<tr>
<td>
{$form.contribution_note.label}<br />
{$form.contribution_note.html}
</td>
</tr>
After debugging I found the query is not making a join to civicrm_note table. Here is the query I got on debugging.
SELECT civicrm_contribution.id, contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.sort_name as `sort_name`, civicrm_contribution.id as contribution_id, civicrm_contribution_recur.id as contribution_recur_id, civicrm_contribution.contribution_status_id as `contribution_status_id`, civicrm_contribution.campaign_id as `contribution_campaign_id`, civicrm_contribution.receive_date as `receive_date`, civicrm_contribution.total_amount as `total_amount`, civicrm_contribution.currency as `currency`, civicrm_contribution.thankyou_date as `thankyou_date`, civicrm_contribution.source as `contribution_source`, civicrm_contribution.is_test as `is_test`, civicrm_contribution.is_pay_later as `is_pay_later`, civicrm_product_en_CA.id as product_id, civicrm_product_en_CA.name as `product_name`, civicrm_product_en_CA.id as `contribution_product_id`, civicrm_contribution.financial_type_id as `financial_type_id` FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id LEFT JOIN civicrm_contribution_recur ON civicrm_contribution.contribution_recur_id = civicrm_contribution_recur.id LEFT JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id LEFT JOIN civicrm_product_en_CA ON civicrm_contribution_product.product_id =civicrm_product_en_CA.id WHERE ( civicrm_note.note = 'check' AND civicrm_contribution.id != 0 ) AND (contact_a.is_deleted = 0) GROUP BY civicrm_contribution.id ORDER BY `receive_date` desc, `contact_a`.`id` LIMIT 0, 50;
Do I require to use QueryObject hook to override the BAO ?
Also, if I use "note" as the field name instead of "contribution_note", it's doing the lookup for notes associated with the contact rather than contribution notes.
