How do I instantiate objects based on a query across many database tables

[Hgiel]

Hi everyone

I'm experimenting with OO by reworking an old procedural coded system. The system is based on users (participants) making preferences for sites they would like to be placed for study.

I have no problem setting up DAO's for accessing single tables/records, but I am struggling with how to setup code to deal with database queries across multiple tables and records.

I have described the scenario below if you want to refer to it, but basically I need to query multiple records across multiple tables, but don't know how to go about setting up the myriad of objects to represent this.

Thanks in advance!
Leigh

Scenario:
I have a screen for an administrator to view a participant's preferences. The preferences displayed are also based on a 'form section' filter.

Eg a preference form could have 2 sections: Section A and Section B ... if Section A is selected, only display the preferences made for this section

I have attached an image below of 5 tables required to get this information.

site: a building or facility
unit: a department within a site where a participant gets placed.
pref_form_section: stores the possible sections that can make up the preference form
preference_option: stores the preference options (related by unit_id) and the section they appear under
preference: stores the preferences that a participant has made.

To be able to list all of the selected participant's preferences, the query would be:

SELECT *
FROM preference
INNER JOIN preference_option ON preference.preference_option_id = preference_option.preference_option_id
INNER JOIN unit ON preference_option.unit_id = unit.unit_id
INNER JOIN site ON unit.site_id = site.site_id

WHERE preference.participant = #form.participant_id#
AND preference_option.pref_form_section_id = #form.pref_form_section_id#

ORDER BY preference.preference_number