Covert MySQL Query to Editor LEFT OUTER JOIN???

tron2101

Working SQL query :

 "SELECT  tblunit.id, tblunit.unitnumber, 
   tblcampingyear.campingyear, 
   tbleventtype.eventtype, 
   tblcamps.campname, 
   tblcamping.dateend, 
   tblcamping.datestart, 
   tbldistricts.districtnumber, 
   tbldistricts.districtname , 
   tblunittype.unittype, 
   tblcamping.youthcnt, 
   tblcamping.adultcnt, 
   tblcamping.nonmembercnt 
 FROM 
   tblunittype 
   INNER JOIN tblunit ON (tblunittype.id = tblunit.fkunittype) 
   INNER JOIN tblcamping ON (tblunit.id = tblcamping.fkunit) 
   INNER JOIN tbldistricts ON (tblunit.fkdistrict = tbldistricts.id) 
   LEFT OUTER JOIN tblcampingyear ON (tblcamping.datestart <= tblcampingyear.enddate) 
 AND (tblcamping.datestart >= tblcampingyear.startdate) 
   INNER JOIN tblcamps ON (tblcamping.fkcamp = tblcamps.id) 
   INNER JOIN tbleventtype ON (tblcamping.fkeventtype = tbleventtype.id); "; 

Editor instance :

 // Build our Editor instance and process the data coming from _POST 
 Editor::inst($db, 'tblcamping', 'id') 
     ->fields( 
         Field::inst('tblcamping.fkunit') 
             ->validator('Validate::notEmpty'), 
         Field::inst('tblunit.unitnumber'), 
         Field::inst('tblcamping.fkcamp') 
             ->validator('Validate::notEmpty') 
             ->options('tblcamps', 'id', 'campname'), 
         Field::inst('tblcamps.campname'), 
         Field::inst('tblcamping.fkeventtype') 
             ->validator('Validate::notEmpty') 
             ->options('tbleventtype', 'id', 'eventtype'), 
         Field::inst('tbleventtype.eventtype'), 
         Field::inst('tblcamping.datestart') 
             ->validator('Validate::notEmpty') 
             ->validator('Validate::dateFormat', array('format' => 'm/d/Y')) 
             ->getFormatter('Format::date_sql_to_format', 'm/d/Y') 
             ->setFormatter('Format::date_format_to_sql', 'm/d/Y'), 
         Field::inst('tblcamping.dateend') 
             ->validator('Validate::notEmpty') 
             ->validator('Validate::dateFormat', array('format' => 'm/d/Y')) 
             ->getFormatter('Format::date_sql_to_format', 'm/d/Y') 
             ->setFormatter('Format::date_format_to_sql', 'm/d/Y'), 
         Field::inst('tblcamping.youthcnt') 
             ->validator('Validate::notEmpty') 
             ->validator('Validate::numeric'), 
         Field::inst('tblcamping.adultcnt') 
             ->validator('Validate::notEmpty') 
             ->validator('Validate::numeric'), 
         Field::inst('tblcamping.nonmembercnt') 
             ->validator('Validate::notEmpty') 
             ->validator('Validate::numeric'), 
         Field::inst('tblunit.fkunittype') 
             ->options('tblunittype', 'id', 'unittype'), 
         Field::inst('tblunittype.unittype'), 
         Field::inst('tblunit.fkdistrict') 
             ->options('tbldistricts', 'id', 'districtname'), 
         Field::inst('tbldistricts.districtname'), 
         Field::inst('tblcampingyear.campingyear') 
     ) 
     ->leftJoin('tblunitdisplay', 'tblunitdisplay.id', "=", 'tblcamping.fkunit') 
     ->leftJoin('tblcamps', 'tblcamps.id', "=", 'tblcamping.fkcamp') 
     ->leftJoin('tbleventtype', 'tbleventtype.id', "=", 'tblcamping.fkeventtype') 
     ->leftJoin('tblunit', 'tblunit.id', "=", 'tblcamping.fkunit') 
     ->leftJoin('tblunittype', 'tblunittype.id', "=", 'tblunit.fkunittype') 
     ->leftJoin('tbldistricts', 'tbldistricts.id', "=", 'tblunit.fkdistrict') 
     ->where(function ($q) { 
         $q->where('date', 'tblcamping.datestart', '<= ', 'tblcampingyear.enddate'); 
         $q->where('date', 'tblcamping.datestart', '>= ', 'tblcampingyear.startdate'); 
     }) 
     ->process($_POST) 
     ->json(); 

tblCamping structure :

 CREATE TABLE tblcamping ( 
   id int(10) NOT NULL, 
   fkunit int(10) DEFAULT NULL, 
   fkcamp int(10) DEFAULT NULL, 
   fkeventtype int(10) DEFAULT NULL, 
   datestart datetime DEFAULT NULL, 
   dateend datetime DEFAULT NULL, 
   youthcnt int(10) NOT NULL DEFAULT '0', 
   adultcnt int(10) NOT NULL DEFAULT '0', 
   nonmembercnt int(10) NOT NULL DEFAULT '0' 
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Any ideas what I've done wrong?

allan

Hi,

The issue here is that the where condition is being applied to the full result set rather than to just the join statement which is what your SQL query at the top of the page has.

What needs to happen is that instead of having the where() method, the complex join condition is used as part of that join statement. Now Editor's API doesn't currently have a clearly defined why of doing that (I'm still deciding on the best way to present an API to do that), however, if you have a look at this discussion it details how complex join expressions can be used with the current API.

Regards,
Allan

p.s. You only need to use the code quoting on the line before and at the end of the code block rather than at the start and end of every line in the block :-)

tron2101

Perfect, works!

->leftJoin( 'tblcampingyear','(tblcamping.datestart <= tblcampingyear.enddate AND tblcamping.datestart >= tblcampingyear.startdate','','')