Complex queries in Data Sources

Suite and Optimus Suite 5
#1

Like everyone I populate grids using the grid.locad(“connector.php”);
Where “connector.php” is something like:

$sql = “SELECT, … JOIN … WHERE … ORDER BY … etc”;

$grid = new GridConnector($res);
$grid->dynamic_loading(100);
$grid->render_sql($sql,“key”," … fields");

UNFORTUNATELY, IF the sql is at all complicated (i.e. joins, etc…), all the grid filtering and sorting commands stop working

eg.
ordGrid.attachHeader("#connector_select_filter,…
ordGrid.setColSorting("connector,…

The only way I have found round this problem is to create a view, with all the joins, and then give the sql “SELECT * FROM view …”. Unfortunately, as the tables get bigger, performance gets worst.

The strange thing is that sometimes the sql I put gets refused by the connector as in error, whereas exactly the same statement runs fine when used directly against mysql.

Can you give any suggestions?

#2

The strange thing is that sometimes the sql I put gets refused by the connector as in error
It can be solved by using

$grid->render_complex_sql($sql);

It will accept any SQL, but will be able to use it only for data loading ( no data saving|filtering|sorting )

If the sql is at all complicated (i.e. joins, etc…), all the grid filtering and sorting commands stop working

Normally multiple joins must not be the problem - try to enable logs and check which sql queries are generated for the filtering. If you are using sub-selects in the sql - it may be a problem, because connector can’t parse them well enough

Please PM me with full sql code for which issue occurs

#3

Thank you Stanislav for your kind answer.

The problem is not so much whether it can render the sql or not. This it does. The problem is that the grids filters and sorts stop working. The moment you try to filter the grid empties, probably the query fails… I presume that the connector takes my query

SELECT …
FROM …
JOIN …
WHERE …
ORDER BY …

then wraps it up with two brackets and adds the filters, i.e

SELECT *
FROM (
SELECT …
FROM …
JOIN …
WHERE …
ORDER BY …) a
WHERE …
ORDER BY …

Somehow the NEW sql (the one regenerated by the connector) fails!

#4

OK, here is a sample and the error i get: Needless to say the query runs fine until the connector tries to add the "COUNT(*) as DHX_COUNT "

I AM using the “render_complex_sql”

====================================
Log started, 23/11/2012 01:50:12

select o.ordID AS ordID
, if((ifnull(o.spunta,0) = 0),’-’,‘X’) AS Spunta
, o.CHIUSO AS STATO
, o.PrevID AS PrevID
, o.ANNO AS ANNO
, o.NUMERO AS NUMERO
, o.DATA AS DATA
, o.DATA_DECO AS DATA_DECO
, o.DATA_REST_PR AS DATA_REST_PR
, o.DATA_DISDETT AS DATA_DISDETT
, o.NOMINATIVO AS NOMINATIVO
, o.SPI AS SPI
, o.NF AS NF
, o.PROROGA AS PROROGA
, o.IND_DEST AS IND_DEST
, o.CAP_DEST AS CAP_DEST
, o.CITTA_DEST AS CITTA_DEST
, o.PROV_DEST AS PROV_DEST
, ifnull(z.zoneDescr, ‘***’) AS zoneDescr
, ifnull((select sum(orddet.QTA) from orddet where (orddet.OrdID = o.ordID)),0) AS TBAGNI
, o.INTERVALLO_P AS INTERVALLO_P
, if(isnull(p.PrevId), ‘’, concat(s.prv, ‘-’, p.ANNO, ‘-’, right(concat(‘0000’, p.NUMERO), 4))) AS Preventivo
from (((ordini o
left join prevhead p on((p.PrevId = o.PrevID)))
left join sedi s on((s.sedeId = p.COD_FILIALE)))
left join zone z on((z.zoneId = o.zoneId)))
WHERE o.COD_FILIALE = 1
ORDER BY anno DESC, NUMERO DESC

COUNT(*) as DHX_COUNT

!!!Uncaught Exception
Code: 0
Message: MySQL operation failed
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘COUNT(*) as DHX_COUNT’ at line 1

#5

Ok, so strangely, at least running the php directly from the browser (i.e. NOT passing it to the grid), it works WITHOUT using the render_complex_sql but just render_sql. However, using the complex sql statement instead of just selecting from a query gives EXACTLY the same results (when php viewed in browser), except for the "< row id=“xxxxx”> as follows:

query from view results :





















query from complex sql results





















notice now the row id goes from:

which works, to:

which doesn’t populate the grid!

Render SQL statement
$grid->render_sql($sql,“ordId”,“STATO
, ANNO
, NUMERO
, DATA
, DATA_DECO
, DATA_REST_PR
, DATA_DISDETT
, NOMINATIVO
, SPI
, NF
, PROROGA
, IND_DEST
, CAP_DEST
, CITTA_DEST
, PROV_DEST
, zoneDescr
, TBAGNI
, INTERVALLO_P
, PREVENTIVO
, SPUNTA”);

#6

It seems - you are using dynamic loading, right ?

Try to modify in sql code the next line

from (((`ordini` `o`

as

_from_ (((`ordini` `o`

and use render_sql.

SQL code have mutliple from instructions, which is confusing parser. Marking top level from in above way must resolve the issue.

#7

Hi Stanislav
i had the same issue but i couldnt fix it from the code that you have said.
Can you please help me out?
I have the same error in my log

!!!Uncaught Exception
Code: 0
Message: MySQL operation failed
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘COUNT(*) as DHX_COUNT’ at line 1

This is how my query looks
$sql = “SELECT li.*,
p.street,p.longitude,p.latitude,
pi.status,pi.type FROM (
SELECT id,created_on,priority,
from information ORDER BY created_on DESC LIMIT 25) as li
INNER JOIN address AS p ON li.id = p.lid
INNER JOIN pinformation AS pi ON li.id = pi.lid”;

$gridConn->render_complex_sql($sql, ‘id’, ‘,street,longitude,latitude,status,type’);

Thanks

#8

render_complex_sql will not modify the SQL query in any way, and will use it as is. But it has a limitation, you can’t use render_complex_sql with dynamic data loading.

As possible solution, you can use the above query to create a view in MySQL ( MariaDB ) and later use render_table(view_name, … ) to fetch the data.

#9

Hi Stanislav
Thanks for reply.
But I couldnt get this
" create a view in MySQL ( MariaDB ) and later use render_table(view_name, … ) to fetch the data."
I would be very much thankful if you please elaborate more on this and provide me some samples
Thanks

#10

MySQL allows to create views, which are kind of virtual tables. You can use any SQL to create a view

When view created, you can work with it as with table. It can be used in the render_table command.

#11

There are two disadvantages to this approach

A view cannot contain sub queries, each subqiery has to be a view.

Any filters (Where …) can only be applied to the view as a whole, so, for example, if you have 10 years with of data and you only need to query last weeks, but joining it with about 10 other tables, it first creates a view on the whole lot (10 years !!), joining each row, and only then let’s you select last week’s data!!!

PLEEAASEE someone prove me wrong on these points!!!

#12

Unfortunately, this is the only solution for complex SQL queries.

You can drop connectors and use a custom server-side code, though. Connector just automates some task, the same logic can be recreated with raw code, where you can control all aspects of data processing.