3062 Posts in 864 Topics by 646 members
|
Page:
1
|
Go to End | |
| Author | Topic: | 379 Views |
-
Create Temporary Table
21 July 2012 at 11:34am
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.idINSERT 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 doDB::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. -
Re: Create Temporary Table
22 July 2012 at 2:20am Last edited: 22 July 2012 2:30am
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 ...");
-
Re: Create Temporary Table
24 July 2012 at 7:04am Last edited: 24 July 2012 7:06am
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.
| 379 Views | ||
|
Page:
1
|
Go to Top |
