Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    8

    Unanswered: 2D Interaction storage

    i'm having problems with storing interactions into a mysql db, let me explain what i see under interactions

    i have the following tables:
    - blocks (stores about 5k records)
    - interactions

    the blocks is just a simple list of names and there ids
    - id (autoincrement)
    - name

    the interactions is a list of interactions between 2 blocks
    - id (autoincrement)
    - blockid_1
    - blockid_2
    - value

    Now i need to retrieve this list and display it as a table
    column headers and the first row is a list off all blocks, and the remaining rows will display the interactions (if there is one) between the column and the row header.

    At the moment i have some performance issues when i try to display this table, for every interaction i do a separated sql querry, this means a lot of queerys and a verry big load time.

    is there a way to retrieve all this info at once? any suggestions on how i can speed things up?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please give a few sample rows, then show what the query result set should look like for those rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    8
    blocks
    1 - b1
    2 - b2
    3 - b3
    4 - b4

    interactions
    1 - 1 - 2 - int1
    2 - 1 - 3 - int4
    3 - 1 - 4 - int5
    4 - 2 - 4 - int6

    Resultset
    b1 b2 b3 b4
    b1 int1 Int4 int5
    b2 int6
    b3
    b4

    bascally i need an easy way to build a table that looks like the result, would be cool if this could be done in 1 querry b ut i doubt it ....

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's what i was afraid of

    i don't think any query you could possibly write will be able to handle a crosstab result set with 5k columns

    and anyhow, i feel it is wrong to do this with SQL -- creating data layouts should be the responsibility of the application

    get the data out quickly, easily, with a single join query, not a query inside a loop


    Edit: added link

    Last edited by r937; 04-20-09 at 16:03.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2009
    Posts
    8
    hmm, is it possible to do this with dynamic columns headers?

    because in the blocks table there can be a lot off culumns, all cross table joins i can find are with static columns ....

  6. #6
    Join Date
    Mar 2009
    Posts
    8
    i have something thats working,

    but i get some data 2 or 3 times ...

    select n2.id as id, n2.name as name,(COUNT(n1.id)-1) as depth, if( (n2.lft+1) = n2.rgt, 0, 1) as haskids,
    GROUP_CONCAT(distinct n1.id order by n1.lft SEPARATOR '-') as pht, group_concat(d.status), group_concat(d.owner)
    , group_concat(if ( i.block_id_1 = n2.id, i.block_id_2, i.block_id_1)) as top
    FROM block AS n1
    INNER JOIN block n2 ON n2.lft BETWEEN n1.lft AND n1.rgt
    LEFT JOIN interaction AS i ON n2.id = i.block_id_1 OR n2.id = i.block_id_2
    LEFT JOIN interaction_data AS d ON i.id = d.interaction_id
    WHERE n2.lft BETWEEN (SELECT lft FROM block WHERE id = 1) AND (SELECT rgt FROM block WHERE id = 1) AND n2.build_id <= 1000 OR n2.build_id = 1000
    GROUP BY n2.id ORDER BY n2.lft

    the group_concats on all fields from the interaction_data table are seen 2,3 or 4 times any idea why?

    EDIT: nevermind see my other post about group by
    Last edited by cereal; 04-21-09 at 08:38.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •