How do I create a temporary table from a select statement from other tables in SIlverstripe?
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.