Jump to:

3460 Posts in 1064 Topics by 739 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Create Temporary Table

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 701 Views
  • moloko_man
    Avatar
    Community Member
    72 Posts

    Create Temporary Table Link to this post

    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: 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.

  • martimiz
    Avatar
    Forum Moderator
    1086 Posts

    Re: Create Temporary Table Link to this post

    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

  • moloko_man
    Avatar
    Community Member
    72 Posts

    Re: Create Temporary Table Link to this post

    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.

    701 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.