Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

Create Temporary Table


Reply


3 Posts   794 Views

Avatar
moloko_man

Community Member, 72 Posts

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.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: [url]http://pastie.org/private/b2jxxlgeklt8fno47b8qba[/url]
They are structured this way because I receive a JSON file that has this structure so its easy to insert into the DB.

Avatar
martimiz

Forum Moderator, 1132 Posts

22 July 2012 at 2:20am

Edited: 22/07/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 ...");

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

Avatar
moloko_man

Community Member, 72 Posts

24 July 2012 at 7:04am

Edited: 24/07/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.