|
goingsmart
|
July 20, 2017 at 7:24 pm
Did not find anything in Forum.
It appears that the Custom Question Answer is only associated with the primary registrant based on the REG_ID.
Is there a way to associate a Custom Question Answer with all Registrants in the Transaction (Checkout Session)
THX.
|
|
goingsmart
|
July 20, 2017 at 8:08 pm
More details:
Primary Registrant will answer question e.g. Meeting Location selected from dropdown list. Need to know that any other Attendees in the Checkout are also associated with the selected question location (ANS_value) to get a location count and attendee list by Ticket Name. SQL Query using EVT_ID and QST_ID only associates with Primary Ticket (but can get counts per REG_group_count for REG_count = 1.
It looks like TXN_ID is the only common link?
|
|
Josh
|
July 21, 2017 at 7:57 am
Yes and you can use the built in model system which will handle the queries for you. There’s some more information about how to use the model system in the developer documentation:
https://github.com/eventespresso/event-espresso-core/blob/master/docs/G–Model-System/model-querying.md
https://github.com/eventespresso/event-espresso-core/blob/master/docs/G–Model-System/using-ee4-model-objects.md#getting-fields-values-from-model-objects-in-various-formats
|
|
goingsmart
|
July 24, 2017 at 8:34 am
It looks to me that the EE4 Model currently does not support what I need since Questions are only linked to the Primary Registrant. In the future, including an index for TXN_ID in wp_esp_answer will allow associating questions with all Registrants in the Checkout (i.e. Checkout Questions). Please add this request to your Roadmap.
Of course, there is also the notion of capturing questions for each Attendee in the Checkout to make customization even more granular. To me that would be a separate optional class of Attendee Questions. Maybe even an Ad-On?
|
|
Josh
|
July 24, 2017 at 9:11 am
The EE4 Model does support this because the EE_Belongs_To_Relation model type has a foreign key to the other model (so for example, EEM_Registration will have a foreign key to EEM_Transaction).
|
|
goingsmart
|
July 24, 2017 at 3:40 pm
I understand EE4 has REG_ID and TXN_ID in wp_esp_registration, and REG_ID in wp_esp_answer. So I am able to get Questions for the Primary Attendee (of course). I also understand that I can identify all Registrations associated with the TXN_ID. I don’t see how I can easily relate the non-Primary Attendees to Questions without TXN_ID in wp_esp_answers.
Since my current need is Reporting, I’m thinking about building (extracting) a wp_esp_answer_mapper table with all the REG_ID associated with TXN_ID. Any other suggestions will be appreciated?
|
|
Josh
|
July 25, 2017 at 11:46 am
Same suggestion: You can use the model system for this and it will handle much of the querying for you without any adding tables. Here’s a gist that has some example code that outputs a list of all attendees for a specific event on the event page and each attendee in the list includes the answer from the primary registrant of their group:
https://gist.github.com/joshfeck/5a16714f39f1392ca741a81c578a7102
|
|
goingsmart
|
July 25, 2017 at 4:04 pm
THX Josh… very helpful for using in EE… which I will do. My immediate need is to extract the EE data via SQL to use with an external reporting engine. So I will need to study the EE model to figure out what EE is doing. Looks like maybe a FORCE INDEX?
So far my SQL is good for the order (Primary Attendee) but I’m not able to get Answer for all Attendees. Any snippets of SQL are welcome. Here’s what I have so far:
SELECT
wp_posts.post_title as wp_posts_post_title,
wp_esp_answer.ANS_value as wp_esp_answer_ANS_value,
wp_esp_transaction.TXN_paid as wp_esp_transaction_TXN_paid,
wp_esp_registration.TXN_ID as wp_esp_registration_TXN_ID,
wp_esp_registration.REG_date as wp_esp_registration_REG_date,
wp_esp_registration.REG_paid as wp_esp_registration_REG_paid,
wp_esp_registration.REG_code as wp_esp_registration_REG_code,
wp_esp_registration.REG_group_size as wp_esp_registration_REG_group_size,
wp_esp_attendee_meta.ATT_fname as wp_esp_attendee_meta_ATT_fname,
wp_esp_attendee_meta.ATT_lname as wp_esp_attendee_meta_ATT_lname,
wp_esp_attendee_meta.ATT_email as wp_esp_attendee_meta_ATT_email
FROM wp_esp_registration
LEFT OUTER JOIN wp_posts ON wp_esp_registration.EVT_ID=wp_posts.ID
LEFT OUTER JOIN wp_esp_transaction ON wp_esp_registration.TXN_ID=wp_esp_transaction.TXN_ID
LEFT OUTER JOIN wp_esp_answer ON wp_esp_registration.REG_ID=wp_esp_answer.REG_ID
LEFT OUTER JOIN wp_esp_attendee_meta ON wp_esp_registration.ATT_ID=wp_esp_attendee_meta.ATT_ID
WHERE ( wp_esp_registration.REG_dateBETWEEN ‘2017-07-01 07:45:00’ and ‘2018-01-01 07:44:00’ ) AND ( wp_esp_registration.STS_IDLIKE ‘RAP’ ) AND ( wp_posts.IDLIKE ’41’ ) AND ( wp_esp_registration.REG_countLIKE ‘1’ ) AND ( wp_esp_answer.QST_IDLIKE ’32’ )
ORDER BY wp_posts.post_title ASC, wp_esp_answer.ANS_value ASC,
wp_esp_attendee_meta.ATT_lname ASC, wp_esp_attendee_meta.ATT_fname ASC
|
|
Josh
|
July 26, 2017 at 8:32 am
You can inspect the generated queries by adding the show_next_x_db_queries() function your PHP debugging code:
https://github.com/eventespresso/event-espresso-core/blob/master/docs/G–Model-System/model-querying.md#inspecting-generated-queries
You might also consider using the EE4 REST API to get data to your external reporting engine:
https://github.com/eventespresso/event-espresso-core/blob/master/docs/C–REST-API/ee4-rest-api-GET-including-specific-fields-and-related-entities-in-results.md
|
|
goingsmart
|
July 29, 2017 at 5:45 pm
All good ideas. Thank you for the feedback.
For now, my TEMP solution was to: Step-1 Add ANS_value fields to Registrations and Transaction. Step-2 add Answer to Registration for Primary Attendee. Step-3 Add Registration Answer for Primary Attendee to Transaction. Step-4 create mysql events to recurring process Step-2 and Step-3. Step-5 The Transaction is used to link all Registrations in the Checkout to the Primary Attendee Answer.
|
