Sub queries for DHTMLX

Suite and Optimus Suite 5
#1

Hi all
I am facing a problem about sub queries. Assuming, I have below code to get data form MySQL:

$grid->render_sql( "SELECT distinct gf.id as id , `name` as entry, gf.description, version, gf.type,date, bug_count, bug_ids, g.totalruns, g.owner, status, comment, release_note FROM generator g,generator_family gf WHERE g.generator_family_id = gf.id AND gf.current_version_id = g.id AND (gf.name like '%%%' OR gf.description like '%%%' OR gf.id in (SELECT distinct generator_family_id FROM generator_category WHERE category like '%%%')) order by g.date desc" , "id", "ids,packageinfo,description,entry,version,type,date,bug_count, totalruns,owner,status,comment,release_note");

As you see, I have used a sub-query my code. But it seems that DHTMLX has not supported sub-query? (hoping that my opinion is not correct :mrgreen: ).
If I remove sub-query from my code, DHTMLX will work well.

Please help me !!!
Thanks so much

#2

Grab connector 1.1
dhtmlx.com/x/download/regula … hp_dvl.zip
and use
$grid->render_complex_sql(…

it will work with any valid sql code, but only for data loading ( updates, server side sorting and filtering - will not work )

#3

It means that this version only support below SQL query:
SELECT * FROM myTable WHERE myConditions (include sub-query) ORDER BY … LIMIT …

and do not support:
INSERT INTO table_nameVALUES (value1, value2, value3,…)

UPDATE table_name SET column1=value, column2=value2,… WHERE some_column=some_value

DELETE FROM table_name WHERE some_column=some_value

Thanks so much

#4

Hi stanislav,

I experienced same problem here, I am using sub query as described below, it work perfectly on grid data loading, but error on filtering, I see that connector confused with WHERE clause, as generated query after filter missing the last WHERE syntax

SELECT id, '' as dummy_1,'' as dummy_2, a.person, concat('<img title=\"',country_name,'\" src=\"asset/imgs/flags/',country_flag,'\" /> ',country_name) as country_name, a.number, '' as action , 
                concat( '', 
                    (IFNULL((
                        SELECT group_concat( tag_name SEPARATOR ',' )
                        FROM person_tags p
                        JOIN tags t ON p.tag_id = t.id
                        WHERE p.person_id = a.id),'')
                    )
                ) AS tags
                FROM addressbook a JOIN country_code c ON a.country=c.country_code

generated query after filtering

SELECT id, '' as dummy_1,'' as dummy_2, a.person, concat('<img title=\"',country_name,'\" src=\"asset/imgs/flags/',country_flag,'\" /> ',country_name) as country_name, a.number, '' as action , 
                concat( '', 
                    (IFNULL((
                        SELECT group_concat( tag_name SEPARATOR ',' )
                        FROM person_tags p
                        JOIN tags t ON p.tag_id = t.id
                        WHERE p.person_id = a.id),'')
                    )
                ) AS tags
                FROM addressbook a JOIN country_code c ON a.country=c.country_code AND person LIKE '%test%' -- <-- here is where the WHERE is missing
#5

and do not support:
INSERT INTO table_nameVALUES (value1, value2, value3,…)

In render_sql you need to provide select command, connector will generate insert query automatically, or you can define custom one through related api

docs.dhtmlx.com/doku.php?id=dhtm … ex_updates

#6

In case of mysql you can create view based on your sql query and use render_table command with name of such view

#7

Hi Stanislav,

what a great solution and how stupid i am :smiley:

thanks :smiley:

#8

Hi Stanislav am interested in purchasing the current version , a doubt already supports stored procedure with server side filters , dynamic loading , updates ??

$grid->render_complex_sql(…

it will work with any valid sql code, but only for data loading ( updates, server side sorting and filtering - will not work )

thank you

#9

render_complex_sql will exec the provided SQL for data generation, it will not try to auto-generate SQL code for data savint. Still you can use server side events or define custom sql code for CRUD operations

docs.dhtmlx.com/connector__php__ … lexqueries

Also, please beware that components do not limited to connector, it just a ready to use solution, but it can be easily replaced with REST like server side API.