Quick version:
How do I create a temporary table from a select statement from other tables in SIlverstripe?
Long version:
I have 8 DataObjects that are related with has_many and has_one relations only, and I'm outputting data from these DOs on a front end page. I want to create a temporary table to get all the data I need from several different tables then just return the temp table. I've looked at MySQLDatabase.php and DB.php and see the basic structure of how to create it, but I want to do something similar to:
CREATE TEMPORARY TABLE review
SELECT m.id, m.name
FROM match m
INNER JOIN stage ON stage.match_id = m.id
INNER JOIN score ON score.match_id = m.id
GROUP BY m.id
INSERT INTO review
SELECT DISTINCT m.id, m.name
FROM match m
IINNER JOIN stage ON stage.match_id = m.id
INNER JOIN score ON score.match_id = m.id
GROUP BY m.id
HAVING COUNT(m.id) > 4;
How would I go about doing this?
Yes I know you can do
DB::createTable('review', $fields, $indexes, 'temporary');
but it's getting the fields in that is confusing me.
Here are my DataObjects: http://pastie.org/private/b2jxxlgeklt8fno47b8qba
They are structured this way because I receive a JSON file that has this structure so its easy to insert into the DB.