1. #1
    Ext User
    Join Date
    Jul 2009
    Posts
    13
    Vote Rating
    0
    loopb is on a distinguished road

      0  

    Question Help with grid (cube)

    Help with grid (cube)



    I have a 2 tables. One With Classes (items) and the second one with classes "day prices"

    I want to do a Grid (cube?) (editable) with:
    - X headers date - MSH_PRICES_DATE
    - Y header class name - MSH_CLASSES_NAME
    - and XY values = MSH_PRICES_PRICE

    can someone help me with that ;/

  2. #2
    Sencha - Community Support Team jay@moduscreate.com's Avatar
    Join Date
    Mar 2007
    Location
    DC Area =)
    Posts
    16,364
    Vote Rating
    81
    jay@moduscreate.com is a name known to all jay@moduscreate.com is a name known to all jay@moduscreate.com is a name known to all jay@moduscreate.com is a name known to all jay@moduscreate.com is a name known to all jay@moduscreate.com is a name known to all

      0  

    Default


    you first need to figure out how to get the stuff from the SQL query to JSON from a HTTP request.

  3. #3
    Ext User
    Join Date
    Jul 2009
    Posts
    13
    Vote Rating
    0
    loopb is on a distinguished road

      0  

    Default


    Yep, that's true
    but it's posible?

    I want to generate x,y headers of grid Dynamic like this:


  4. #4
    Sencha - Community Support Team Condor's Avatar
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    24,246
    Vote Rating
    97
    Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of

      0  

    Default


    Can your database transform a table? That would be the easiest solution.

    Otherwise you will have to transform the data either on the server or on the client (in javascript).

  5. #5
    Ext User
    Join Date
    Jul 2009
    Posts
    13
    Vote Rating
    0
    loopb is on a distinguished road

      0  

    Default


    no I use mysql 5.0 and inodb tables.
    i think there is a wy to do Transform, Pivot(?) or Crosstab(?) in mysql
    but my knowledge about it is insignificant

    ouh and i use php5&mysql5&extjs3

    // i think it could look like this http://www.smartclient.com/#basicCube

    so i think I could select values or create a view

    SELECT p.MSH_CLASSES_NAME, o.MSH_PRICES_DATE, o.MSH_PRICES_PRICE
    FROM MSH_CLASSES p, MSH_PRICES o
    WHERE o.MSH_PRICES_MSH_CLASSES_ID = p.MSH_CLASSES_ID;

    and use then AdoDB Pivot statement http://phplens.com/adodb/pivot.tables.html, could I?
    Last edited by loopb; 22 Aug 2009 at 7:13 AM. Reason: added a link and sql

  6. #6
    Sencha - Community Support Team Condor's Avatar
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    24,246
    Vote Rating
    97
    Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of

      0  

    Default


    Do you really want to create the overhead of using ADO instead of a direct MSSQL connection?
    Maybe you should transform the data in PHP before you send it to the client.

  7. #7
    Ext User
    Join Date
    Jul 2009
    Posts
    13
    Vote Rating
    0
    loopb is on a distinguished road

      0  

    Default


    now I don't know


    whats the best way?
    directly from mysql create a statement to create w pivot(?)?
    i realy don'u use it so i dont know how to create one

    pass the data by json to ext?
    create grid and view the data?

  8. #8
    Ext User
    Join Date
    Jul 2009
    Posts
    13
    Vote Rating
    0
    loopb is on a distinguished road

      0  

    Default


    ok, i can get headers like this

    Code:
    SELECT GROUP_CONCAT(DISTINCT
    CONCAT('\nCOUNT(CASE WHEN c.MSH_PRICES_DATE= "',
    c.MSH_PRICES_DATE, '" THEN id ELSE NULL END) AS
    ', c.MSH_PRICES_DATE)) AS column_list
    FROM MSH_PRICES c;
    i will get something like this

    Code:
    COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-16" THEN id ELSE NULL END) AS
    2009-08-16,
    COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-17" THEN id ELSE NULL END) AS
    2009-08-17,
    COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-18" THEN id ELSE NULL END) AS
    2009-08-18,
    COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-19" THEN id ELSE NULL END) AS
    2009-08-19,
    COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-20" THEN id ELSE NULL END) AS
    2009-08-20
    i can use it to my SQL pivot

    soo

    Code:
    SELECT o.MSH_CLASSES_NAME
    
    { column_list }
    
    FROM MSH_CLASSES o
    WHERE o.MSH_CLASSES_ID = c.MSH_PRICES_MSH_CLASSES_ID
    GROUP BY o.MSH_CLASSES_NAME
    how to use { column_list } ?

    do i think the right way?
    Last edited by loopb; 30 Aug 2009 at 10:22 AM. Reason: sql edit

  9. #9
    Sencha - Community Support Team Condor's Avatar
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    24,246
    Vote Rating
    97
    Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of Condor has much to be proud of

      0  

    Default


    It might be faster to do the counting in PHP, but your SQL will indeed work.

    Since the number of columns and their names are now variable you will need to send metaData along with the JSON data to the client (see JsonReader API docs for details).

  10. #10
    Ext User
    Join Date
    Jul 2009
    Posts
    13
    Vote Rating
    0
    loopb is on a distinguished road

      0  

    Default


    I must work wwith my sql

    it works

    Code:
    SELECT (SELECT o.MSH_CLASSES_NAME FROM MSH_CLASSES o WHERE o.MSH_CLASSES_ID = c.MSH_PRICES_MSH_CLASSES_ID) AS CLASSES,
    MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-16" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-16",
    MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-17" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-17",
    MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-18" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-18",
    MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-19" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-19",
    MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-20" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-20"
    FROM MSH_PRICES c
    GROUP BY c.MSH_PRICES_MSH_CLASSES_ID
    LIMIT 0, 30;
    i get from it



    But I have my

    Code:
    SELECT GROUP_CONCAT(DISTINCT CONCAT('\nCOUNT(CASE WHEN c.MSH_PRICES_DATE= "',c.MSH_PRICES_DATE, '" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS', c.MSH_PRICES_DATE)) AS column_list FROM MSH_PRICES c;
    but how i can use it (->column_list) in my first sql? i have tried and i have errors in phpmyadmin ;/
    can help someone?

    this dosn't work i gues i have to use cursor but how can i do this?

    Code:
    SELECT (
        SELECT o.MSH_CLASSES_NAME 
        FROM MSH_CLASSES o 
        WHERE o.MSH_CLASSES_ID = c.MSH_PRICES_MSH_CLASSES_ID
    ) AS CLASSES,(
        SELECT GROUP_CONCAT(DISTINCT CONCAT(
        'MAX(CASE WHEN c.MSH_PRICES_DATE = 
        "',c.MSH_PRICES_DATE,'"
        THEN c.MSH_PRICES_PRICE ELSE NULL END) AS
        "',c.MSH_PRICES_DATE,'"'))
    )
    FROM MSH_PRICES c
    GROUP BY c.MSH_PRICES_MSH_CLASSES_ID
    LIMIT 0, 30;
    from this i get:

    Last edited by loopb; 1 Sep 2009 at 7:40 AM. Reason: add sql

Thread Participants: 2