Subquery in a Grid's SQL

[lilmofo812]

So I am assuming this doesn't work as I have typed out a mysql query that has subqueries in it (pulls the correct data when run against the database) but when I copy it into the SQL part of a grid's definition it doesn't give me any fields to choose from (which I am assuming is because of the subqueries within the from clause). Does anyone know a way around this? The reason I am doing it this way is I tried to create views to aggregate this data but it became too inefficient as the amount of data is growing at a very fast pace. Also without using subqueries I can't accurately collect summed values that are being pulled from multiple tables based on similar id's across the tables (if you have a way of doing this I would be glad to here it). Any help would be greatly appreciated!

Jmo

[Michael1954]

Re: Subquery in a Grid's SQL

Can you post an example ?

I use multiple views regularly, but the data sets are small.

You could try a stored procedure; years ago when I did not have enough indexes I built "work table". This was just after hard drives became affordable so anything was faster than swapping floppy disks.

Michael

[lilmofo812]

Re: Subquery in a Grid's SQL

I've been able to increase the efficiency of my views with more join parameters so I'm going to first get some more data in my tables and see how this holds up.

A quick example is below:

Select
         table_1.id as id,
         table_1.id_2 as id_2,
         Sum(ifnull(table_1.total,0)) as total_1,
         table_2.total as total_2
From
         table_1 as table_1
Left join
          (Select
                    table_2.id as id,
                    table_2.id_2 as id_2,
                    Sum(ifnull(table_2.total,0)) as total_2
             From
                     table_2
             Group By
                      table_2.id_2,
                      table_2.id) as table_2  On 
                      table_1.id_2 = table_2.id_2
Group By
            table_1.id_2,
            table_1.id

[GGandhi]

Re: Subquery in a Grid's SQL

why not build the query sub query in alpha itself so you can see the progress and get the correct data you need?

[lilmofo812]

Re: Subquery in a Grid's SQL

Alpha doesn't seem to allow the select statement in the from clause...

[GGandhi]

Re: Subquery in a Grid's SQL

Alpha doesn't seem to allow the select statement in the from clause

would you care to explain further.
the way to build a sql statement for the source of the grid is to design and connect all the tables needed. this you have done and alpha is not allowing it?

[GGandhi]

Re: Subquery in a Grid's SQL

SELECT
         table_1.id as id,
         table_1.id_2 as id_2,
         Sum(ifnull(table_1.total,0)) as total_1,
         table_2.total as total_2,
         table_2.id as id,
         table_2.id_2 as id_2,
         Sum(ifnull(table_2.total,0)) as total_2

FROM
         table_1 as table_1

LEFT JOIN
         table_2 as table_2
        on  table_1.id_2 = table_2.id_2
GROUP BY
            table_1.id_2,
            table_1.id

I rewrote what you had, may be this will work better.
I know I have done select subquery in alpha, I will look for it and see if it has any use for you.

[christappan]

Re: Subquery in a Grid's SQL

Josh, you can use the server side onSQLSelectQuery--there are videos on it explaining it, but basically you have to have a query in the main grid query that produces the column names and data type you want but the data comes from the onSQLSelectQuery.

So in the grid query you could just put :

Select
         table_1.id as id,
         table_1.id_2 as id_2,
         0 as total_1,
         0 as total_2
From
         table_1 as table_1

That will give you four numeric columns in your grid fields. And then put the query you actually want to run in the onSQLSelectQuery event. Use the onSQLCountQuery in conjunction with it; the onSQLRefreshRow query gets trickier and I haven't used the onSQSummaryValuesQuery.

[Davidk]

Re: Subquery in a Grid's SQL

What about keeping the SQL Statement in SQL...putting it into a Stored Proc... and using that Stored Proc in the Alpha Query section. I've done that for pivotGrid and that Stored Proc is really complicated.

There's a whole section in the docs labelled "Grid Component - New Server Side Grid Events for stored procedures" that discuss calling and the events you'll need to work with.