"DB Error: no such field" when doing export with address custom field
Overview
When doing export with a custom field on address, i get DB Error: no such field
Reproduction steps
- Create a custom field on address
- Search for contacts
- Action
Export Contact
- Use
Select fields for export
and add the custom field on address - Click
Continue
->DB Error: no such field
Current behaviour
DB Error: no such field
More precisely, here is an example of query that crashes :
SELECT contact_a.id as contact_id, contact_a.do_not_email as `do_not_email`, contact_a.do_not_mail as `do_not_mail`, contact_a.preferred_language as `preferred_language`, contact_a.source as `contact_source`, contact_a.first_name as `first_name`, contact_a.last_name as `last_name`, contact_a.prefix_id as `prefix_id`, contact_a.birth_date as `birth_date`, contact_a.is_deceased as `is_deceased`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`, `Home-address`.id as `Home-address_id`, `Home-address`.supplemental_address_1 as `Home-supplemental_address_1`, `Home-address`.supplemental_address_2 as `Home-supplemental_address_2`, `Home-address`.street_address as `Home-street_address`, `Home-address`.postal_code as `Home-postal_code`, `Home-address`.city as `Home-city`, `Home-address`.country_id as `Home-country_id`, `Home-value_validit_de_l_adresse_postale_41`.id as `Home-value_validit_de_l_adresse_postale_41_id`, `Home-address-custom-181`.adresse_erron_e__181 as `Home-custom_181`, `Home-email`.id as `Home-email_id`, `Home-email`.email as `Home-email`, `Work-location_type`.id as `Work-location_type_id`, `Work-location_type`.name as `Work-location_type`, `Work-email`.id as `Work-email_id`, `Work-email`.email as `Work-email`, `Home-address-custom-181`.id as `Home-address-custom-181_id`, civicrm_value_qualification_du_prospect_29.id as civicrm_value_qualification_du_prospect_29_id, civicrm_value_qualification_du_prospect_29.type_de_prospect_127 as custom_127, civicrm_value_identification_individus__38.id as civicrm_value_identification_individus__38_id, civicrm_value_identification_individus__38.num_ro_nn_12 as custom_12, civicrm_value_identification_individus__38.conjoint_du_donateur_196 as custom_196, civicrm_value_caract_risation_pour_la_prospection_28.id as civicrm_value_caract_risation_pour_la_prospection_28_id, civicrm_value_caract_risation_pour_la_prospection_28.source_prospects_119 as custom_119, civicrm_value_caract_risation_pour_la_prospection_28.profiling_du_prospect_donateur_120 as custom_120, civicrm_value_qualification_du_donateur_30.id as civicrm_value_qualification_du_donateur_30_id, civicrm_value_qualification_du_donateur_30.type_de_donateur_effectif_131 as custom_131, civicrm_value_fundraising_testament_44.id as civicrm_value_fundraising_testament_44_id, civicrm_value_fundraising_testament_44.testament_source_info_206 as custom_206, civicrm_value_fundraising_testament_44.testament_nombre_bonnes_causes_d_211 as custom_211, civicrm_value_fundraising_testament_44.testament_date_du_contact_212 as custom_212, civicrm_value_fundraising_testament_44.testament_montant_estim_attribu__207 as custom_207, civicrm_value_fundraising_testament_44.testament_acte_pass_le_208 as custom_208, civicrm_value_fundraising_testament_44.testament_notaire_ayant_pass_l_a_209 as custom_209, civicrm_value_fundraising_testament_44.testament_commentaire_libre_210 as custom_210, civicrm_value_summary_fields_25.id as civicrm_value_summary_fields_25_id, civicrm_value_summary_fields_25.total_number_of_events_194 as custom_194, civicrm_value_summary_fields_25.number_of_events_attended_203 as custom_203, civicrm_value_summary_fields_25.total_lifetime_dons_101 as custom_101, civicrm_value_summary_fields_25.date_of_first_don_202 as custom_202, civicrm_value_summary_fields_25.date_of_last_don_106 as custom_106, civicrm_value_summary_fields_25.total_dons_last_year_103 as custom_103 FROM civicrm_contact contact_a
LEFT JOIN civicrm_address `Home-address` ON (`Home-address`.contact_id = contact_a.id AND `Home-address`.location_type_id = 1)
LEFT JOIN civicrm_value_validit_de_l_adresse_postale_41 `Home-address-custom-181` ON (`Home-address-custom-181`.entity_id = `Home-address`.id) LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id )
LEFT JOIN civicrm_email `Home-email` ON contact_a.id = `Home-email`.contact_id AND `Home-email`.location_type_id = 1
LEFT JOIN civicrm_email `Work-email` ON contact_a.id = `Work-email`.contact_id AND `Work-email`.location_type_id = 2
LEFT JOIN civicrm_location_type_fr_FR `Home-location_type` ON ( ( `Home-address`.location_type_id = `Home-location_type`.id ) OR ( `Home-address`.location_type_id = `Home-location_type`.id ) OR ( `Home-address`.location_type_id = `Home-location_type`.id ) OR ( `Home-email`.location_type_id = `Home-location_type`.id ) )
LEFT JOIN civicrm_location_type_fr_FR `Work-location_type` ON ( ( `Work-email`.location_type_id = `Work-location_type`.id ) )
LEFT JOIN civicrm_value_caract_risation_pour_la_prospection_28 ON civicrm_value_caract_risation_pour_la_prospection_28.entity_id = `contact_a`.id
LEFT JOIN civicrm_value_fundraising_testament_44 ON civicrm_value_fundraising_testament_44.entity_id = `contact_a`.id
LEFT JOIN civicrm_value_identification_individus__38 ON civicrm_value_identification_individus__38.entity_id = `contact_a`.id
LEFT JOIN civicrm_value_qualification_du_donateur_30 ON civicrm_value_qualification_du_donateur_30.entity_id = `contact_a`.id
LEFT JOIN civicrm_value_qualification_du_prospect_29 ON civicrm_value_qualification_du_prospect_29.entity_id = `contact_a`.id
LEFT JOIN civicrm_value_summary_fields_25 ON civicrm_value_summary_fields_25.entity_id = `contact_a`.id WHERE ( contact_a.preferred_language = 'fr_FR' AND ( IF (civicrm_address.postal_code REGEXP '^[0-9]{1,10}$', CAST(civicrm_address.postal_code AS UNSIGNED), 0) >= '4000' ) AND ( IF (civicrm_address.postal_code REGEXP '^[0-9]{1,10}$', CAST(civicrm_address.postal_code AS UNSIGNED), 0) <= '4999' ) AND
( contact_a.created_date >= '20190101000000' ) AND
( contact_a.created_date <= '20191231235959' )
AND civicrm_value_qualification_du_prospect_29.type_de_prospect_127 RLIKE BINARY '^ATE^A' ) AND (contact_a.is_deleted = 0) AND contact_a.is_deleted != 1 AND contact_a.id IN ( 45489,46728,45901 ) GROUP BY contact_a.id LIMIT 0, 100000
The field that doesn't exist is :
`Home-value_validit_de_l_adresse_postale_41`.id as `Home-value_validit_de_l_adresse_postale_41_id`
Expected behaviour
Should export as expected