Can I use a php function to perform the equivalent of a mySQL left join? -
the problem having using current function below taking long load. 2 tables being joined consist of thousands of rows apiece. specific function holds table join &getfields() function in model section below.
i thinking possibly have function checked whether request edit or add new , deal separating table join on edit queries (no clue how accomplish this).
call page initial page - using html form post method
if(count($this->fields)){ for($i=0;$ifields);$i++){ $field =& $this->fields[$i]; $se = $field->section; $ti = $field->title; $ft = $field->type; $na = $field->name; $fva = $field->field_value; $nu = $field->unit; switch($se){ case 'header' : echo 'input type="blah...
controller directing traffic model
function __construct() { parent::__construct(); $this->registertask('add','edit'); jrequest::setvar('view','cpanel'); } function edit() { jrequest::setvar('layout','form'); parent::display(); } function save() { $model = $this->getmodel('cpanel'); $msg = $model->store(); $link = ; $this->setredirect($link, $msg); }
model holds actual functions
function __construct() { parent::__construct(); global $mainframe; $array = jrequest::getvar('cid', 0, '', 'array'); $this->setid((int)$array[0]); } function setid($id) { // set id , wipe data $this->_id = $id; $this->_data = null; } function &getitem() { if(empty($this->_data)) { $query = ' select * #__directory_entry id = '.$this->_id; $this->_db->setquery( $query ); $this->_data = $this->_db->loadobject(); } if(!$this->_data) { $session =& jfactory::getsession(); $post = $session->get('post'); $this->_data->id = 0; $this->_data->title = isset($post['title'])?$post['title']:null; } return $this->_data; } function &getfields() { $query = 'select f.*, ef.field_value #__directory_field f left join
#__directory_enf ef on (f.id = ef.field_id , ef.entry_id = '.$this->_id.') where
f.published = 1 order f.ordering asc'; $this->_db->setquery( $query ); $fields = $this->_db->loadobjectlist(); $session =& jfactory::getsession(); if($session->has('post') , !$this->_id) { $post = $session->get('post'); for($i=0;$ifield_value = isset($post['field_'.$fields[$i]->name])?$post['field_'.$fields[$i]->name]:null; } return $fields; } function store() { // check request forgeries jrequest::checktoken() or jexit( jtext::_('invalid token') ); $post = jrequest::get( 'post' ); $session =& jfactory::getsession(); $session->set('post', $post); if($post["title"] == "") return jtext::_('please enter title'); $query = 'select * #__directory_field published = 1'; $this->_db->setquery( $query ); $fields = $this->_db->loadobjectlist(); for($i=0;$irequired , $post['field_'.$fields[$i]->name] == "") return jtext::_($fields[$i]->title . ' required field , can not left blank.'); } if(!$post["id"]) { $date =& jfactory::getdate(); $post['create_date'] = $date->tomysql(); $post['ordering'] = 1; $query = 'select ordering #__directory_entry order ordering desc limit 1'; $this->_db->setquery( $query ); $post['ordering'] += $this->_db->loadresult(); $post['published'] = 1; } $me =& jfactory::getuser(); jtable::addincludepath(jpath_administrator.ds.'components'.ds.'com_joomd'.ds.'tables'); $row =& jtable::getinstance('entry', 'table'); if (!$row->bind( $post )) return jtext::_('sorry error occurred.'); if (!$row->store()) return $this->_db->geterrormsg(); if(!$post['id']) { $post['id'] = $this->_db->insertid(); jrequest::setvar('id', $post['id']); $msg = jtext::_('entry added!'); $query = 'insert #__directory_enu (entry_id, user_id) values ('.$post['id'].', '.$me->id.')'; $this->_db->setquery( $query ); $this->_db->query(); } else $msg = jtext::_('entry updated!'); $max_size = 10000000; jimport('joomla.filesystem.file'); $time = time(); for($i=0;$itype == "image") $allowed = array('.jpg', '.jpeg', '.gif', '.png'); elseif($field->type == "file") $allowed = array('.doc', '.docx', '.pdf', '.txt', '.exl', '.xls', '.xlsx', '.jpg', '.jpeg', '.gif', '.png', '.zip'); $flag = true; $query = 'select count(*) #__directory_enf entry_id = ' . $post["id"] .' , field_id = ' . $field->id; $this->_db->setquery( $query ); $count = $this->_db->loadresult(); if($field->type == "image" or $field->type == "file") { $image = jrequest::getvar("field_".$field->name, null, 'files', 'array'); $image_name = str_replace(' ', '', jfile::makesafe($image['name'])); $image_tmp = $image["tmp_name"]; if($image_name "") { $ext = strrchr($image_name, '.'); if(!in_array($ext, $allowed)) return sprintf(jtext::_('file type %s not allowed.'), $field->title); if(filesize($image_tmp) > $max_size) return sprintf(jtext::_('file size %s exceeds maximum file size.'), $field->title); if(move_uploaded_file($image_tmp, jpath_site.'/files/'.$time.$image_name)) $post["field_".$field->name] = $time.$image_name; else return sprintf(jtext::_('sorry file %s not uploaded.'), $field->title); } else $flag = false; } if($flag) { if($count) { $query = 'select id #__directory_enf entry_id = '.$post["id"].' , field_id = ' . $field->id; $this->_db->setquery( $query ); $field_id = $this->_db->loadresult(); $update = new stdclass(); $update->id = $field_id; $update->field_value = $post["field_".$field->name]; $this->_db->updateobject('#__directory_enf', $update, 'id'); } else { $insert = new stdclass(); $insert->id = null; $insert->field_id = $field->id; $insert->entry_id = $post["id"]; $insert->field_value = $post["field_".$field->name]; $this->_db->insertobject('#__directory_enf', $insert, 'id'); } } } $session->clear('post'); return $msg; }
left join fastest method, have written query in such way slow.
select f.*, ef.field_value #__directory_field f left join #__directory_enf ef on (f.id = ef.field_id , ef.entry_id = $this->_id) f.published = 1 order f.ordering asc your on clause main problem. written way ant trailing through every record, , not putting use indexes. want reverse table order, , put id match in clause. this.
select ef.field_value, f.* #__directory_enf ef left join #__directory_field f on f.id = ef.field_id f.published = 1 , ef.entry_id = $this->_id order f.ordering asc you want make sure database has indexes on both f.id , ef.entry_id fields. should make query many many times faster.
Comments
Post a Comment