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');

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.

I don't know why you would want to use a temporary table - but I suppose you want to fire multiple queries on it, once you have created it? In that case could you use a view instead?

As I read it, in MySQL you can create a view based on your initial select query, and then use it as you would a table, as long as you're within the same session:

CREATE VIEW matchview
AS
SELECT m.id, m.name
FROM match m
... 

Anyway, you'd use raw queries, as you're wordking with table data, not a DataObject, so that could be something like:

DB::query("CREATE ...");

http://doc.silverstripe.org/framework/en/reference/sqlquery

This is a step in the right direction, I will need to use some views (or temp tables), but the more I dive into this, the more I realize I need a pivot table that pulls in data from 4+ tables. This should be fun!

I think the best route will be creating a view of what I need then using pivot table to make it viewable on the front end.

If anyone has any experience or knowledge of pivot tables in SilverStripe, I'm all ears.