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   711 Views

Avatar
moloko_man

21 July 2012 at 11:34am Community Member, 72 Posts

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

22 July 2012 at 2:20am (Last edited: 22 July 2012 2:30am), Forum Moderator, 1091 Posts

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

24 July 2012 at 7:04am (Last edited: 24 July 2012 7:06am), Community Member, 72 Posts

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.