Let's say I have 4 models that are related to each other like this: User->Registration->Submission->SubmissionField. That is to say that User has a one-to-many relationship with Registration, Registration has a one-to-many relationship with Submission, and Submission has a one-to-many relationship with SubmissionField.

They all have different purposes for storing data such as the user creates an account so the username and password go in Users, then they register on the site in order to upload a submission so the registration data goes in Registration. Then they upload their submission and that data goes in Submission. The data in the form they filled out for their submission goes in SubmissionField.

Here's the hard part now. Let's say I want to export all data for each user to an excel spreadsheet.
In my controller I could do this:

$this->users =& $this->User->find('all', array(
'joins' => 'INNER JOIN ( SELECT id, site_id FROM registrations) AS registration ON users.id = registration.id',
'conditions' => 'registration.site_id=' . $this->Site->id,
));

But since we are using find() on the User model, I'll only get back the fields from the users table. This wont work.

I could try this:

$this->users =& $this->User->find('all', array(
'include' => array('Registration','Submission','SubmissionField')
));

But again, oops. This wont work either. Submission and SubmissionField arent related to User.

So I'm stuck. I put together good model structure but now the data is stranded. I want to be able to get data from all 4 tables and have it available through one object. How can I join all these tables together and get all/some of the fields returned from the 4 different tables as well? In PHP I could do this by using INNER/LEFT/RIGHT JOINS such as:

SELECT * FROM users
INNER JOIN registrations ON registrations.user_id = users.id
INNER JOIN submissions ON submissions.registration_id = registrations.id,
INNER JOIN submission_fields ON submission_fields.submission_id = submissions.id

and that would give me all my data in one array/object. I cannot seem to get an answer from anyone on how to do the above query in Akelos and I have tried almost everything I can think of. This kind of joining is done almost everywhere on PHP sites and it just seems frustrating and Akelos is limited on doing this sort of functionality. Anyone know of a good way to do this? Please help. Thanks.

-- Pogeybait4883

I have not tried that. Didnt even know that was possible. I'll give that a try Bermi. Thanks for replying.