I'm looking at programming/design patterns for the model layer of my application, and am wondering which one fits best with the situation where you are doing a retrieval that involves joins across multiple tables.
For example, suppose you have the following tables/relations: Customer --> 1..n Accounts --> 0..n Features
where a Feature could be a cheque book, or some premium product like free travel insurance.
Then I want to do getCustomersForFeature() to retrieve all customers with accounts that have free travel insurance.
Using ActiveRecord or Data Access Object doesn't seem to fit, as these generally focus on one class per table; likewise for Data Mapper. I realise I could break it down into operations for each table, e.g. getAccountsForFeature() and getCustomersForAccount(), but I want to do the retrieval in one hit.
If we were to "bend" the one-class-per-table patterns and use the Data Access Object pattern, say, should the getCustomersForFeature() method go on CustomerDAO or FeatureDAO? But this doesn't feel right to me because you would be polluting your DAOs with knowledge of other tables.
Suggestions please.