Start Building Professional
Web Apps Today

connector render_sql complex query

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

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 "<rows pos='".$pos."'>";
         else
          return "<rows total_count='75'>";
   }
}

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. 

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

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.