connector render_sql complex query

Discontinued products Uncategorized discussions
#1

Hello,



I found one problem with the fonction ‘render_sql’.

I’m using dhtmlxgrid ( dhtmlxSuite 2009 Rel.2 (DHTMLX 2.5) Professional edition build 91111 )

with connector (dhtmlxConnector for PHP v.0.96 ) with smartrendering.





$gridConn = new GridConnector($resultConnection,“MySQL”);

$gridConn->set_encoding(“iso-8859-1”);

$gridConn->dynamic_loading(40);

$gridConn->enable_log(“some.txt”);



When i’m loading datas, dhtmlx display only 40 rows, no more. But i have 75 rows.

this is my query:

"

SELECT study.ID as SID,

                    study.StudyDescription,

                    date_format(study.StudyDate,’%d/%m/%Y’) as sd,

                    study.Modality as moda,

                    study.ReferringPhysiciansName,

                    study.NameOfPhysiciansReadingStudy,

                    study.AccessionNumber,

                    study.IDPatient,

                    patient.PatientID,

                    patient.PatientsName,

                    patient.PatientsSex,

                    date_format(patient.PatientsBirthDate,’%d/%m/%Y’) as pbd,

                    study.ReferencedStudySequence,

                    study.RTFReportPath,

                    study.PDFReportPath,

                    COUNT(DISTINCT series.ID) as nbSer,

                    COUNT(image.ID) as nbImg FROM patient

                INNER JOIN study ON (study.IDPatient = patient.ID)

                INNER JOIN series ON (series.IDStudy = study.ID)

                INNER JOIN image ON (image.IDSeries = series.ID)

                GROUP BY study.ID

"



I tested this query with mysql client and it’s OK.

but to get the number of row your query is (get from ‘some.txt’):

"

SELECT COUNT() as DHX_COUNT FROM patient

                INNER JOIN study ON (study.IDPatient = patient.ID)

                INNER JOIN series ON (series.IDStudy = study.ID)

                INNER JOIN image ON (image.IDSeries = series.ID)

                GROUP BY study.ID

"

I have 75 study.ID in my database.

the result of your query is: 75 rows with the number of image by study.ID

but i think you want: 1 row with ‘75’ -> number of study.ID



the right query should be:

"SELECT COUNT() as DHX_COUNT FROM patient

                INNER JOIN study ON (study.IDPatient = patient.ID)" to get ‘75’ -> number of study.ID



thanks for your help,

best regards

#2

Existing version doesn’t support GROUP BY instructions in render_sql

The simples way to workaround problem would be to define your custom class as

class MyGridConnector extends GridConnector{
public function __construct($res,$type=false,$item_type=false,$data_type=false){
parent::__construct($res,$type,$item_type,$data_type);
}
protected function xml_start(){
if ($pos=$this->request->get_start())
return “”;
else
return “”;
}
}

And use the MyGridConnector instead of GridConnector
The 75 in above code can be replaced with some var, which will contain valid count of rows in the dataset.

#3

May i suggest a correction?
tell me what you think about:

file: db_common.php
class: DBDataWrapper
fonction: get_size

after: $data=$this->get_next($res);

add:
if(($countRows = mysql_num_rows($res)) > 1) {
   return $countRows;
}
else {
  if (array_key_exist …;
  else return $data[…; //postgresql
}

thanks,
best regards

#4

It seems that your solution will solve issue as well, and in better way. It must not cause any side-effects
We will run some additional test, and if all will work correctly - it will be added to the main codebase.