Hi!
We're getting our feet wet with xPDO on a small project where we're retrieving a list of names of graduates. There are about 4500 records in our table and we want to "select distinct city from ttu_dglist_names" but it take upwards of 30 seconds to load the page and does not select anything distinct. Sometimes it gives us out of memory errors. What are we doing wrong?
This is the specific bit of code giving us trouble. At the bottom is the whole script in case it helps.
$query = $modx->newQuery("Names");
$query->distinct();
$query->select(array($modx->getSelectColumns("Names", array("city"))));
$query->where(array("category"=>$list_full));
$query->andCondition(array("termcode"=>$term));
$query->sortby("city", "ASC");
$cities = $modx->getCollection("Names", $query);
foreach($cities as $city)
$cityOptions .= $modx->getChunk("deangradlist-menu-city", array("city"=>$city->get("city")));
Schema:
<?xml version="1.0" encoding="UTF-8"?>
<model package="ttu_dglist" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
<object class="Names" table="names" extends="xPDOSimpleObject">
<field key="firstname" dbtype="varchar" precision="250" phptype="string" null="false" />
<field key="middlename" dbtype="varchar" precision="250" phptype="string" null="false" />
<field key="lastname" dbtype="varchar" precision="250" phptype="string" null="false" />
<field key="city" dbtype="varchar" precision="250" phptype="string" null="false" />
<field key="county" dbtype="varchar" precision="250" phptype="string" null="false" />
<field key="state" dbtype="varchar" precision="250" phptype="string" null="false" />
<field key="termcode" dbtype="varchar" precision="250" phptype="string" null="false" />
<field key="category" dbtype="varchar" precision="250" phptype="string" null="false" />
</object>
<object class="Terms" table="terms" extends="xPDOObject">
<field key="termcode" dbtype="varchar" precision="6" phptype="string" null="false" index="pk" />
<field key="description" dbtype="varchar" precision="20" phptype="string" null="false" />
<field key="published" dbtype="tinyint" precision="1" phptype="integer" null="false" default="0" />
<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
<column key="termcode" length="" collation="A" null="false" />
</index>
</object>
</model>
CREATE TABLE:
CREATE TABLE `ttu_dglist_names` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(250) NOT NULL,
`middlename` varchar(250) NOT NULL,
`lastname` varchar(250) NOT NULL,
`city` varchar(250) NOT NULL,
`county` varchar(250) NOT NULL,
`state` varchar(250) NOT NULL,
`termcode` varchar(250) NOT NULL,
`category` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4646 DEFAULT CHARSET=latin1
Full Snippet:
<?php $modx->regClientCSS(MODX_ASSETS_URL."templates/ttuv3/css/ttu_dglist.css");
$output = "";
$items = "";
$query = "";
$list_full = "";
$hdr = "";
$row = 1;
$termslist = "";
$cityOptions = "";
$regionOptions = "";
$path = MODX_CORE_PATH . "components/ttu_dglist/model/";
if(!$modx->addPackage('ttu_dglist', $path, 'ttu_dglist_'))
return 'There was a problem adding your package! Check the logs for more info!';
if(!isset($_GET["term"]) || !isset($_GET["list"])){
$query = $modx->newQuery("Terms");
$query->where(array("published", 1));
$query->sortby("termcode", "DESC");
$terms = $modx->getCollection("Terms", $query);
foreach($terms as $term)
$termslist .= $modx->getChunk("deangradterm", array("description"=>$term->get("description"), "termcode"=>$term->get("termcode")));
$output = $modx->getChunk("deangradterms", array("termslist"=>$termslist));
}else{
$term = $_GET["term"];
$list = $_GET["list"];
//get term name...
$query = $modx->newQuery('Terms');
$query->where(array('termcode:=' => $term) );
$result = $modx->getObject('Terms', $query);
$tname = $result->get('description');
//set full category name and header variable...
if ($list == "dean") { $list_full = "deanslist";
$hdr = $tname . " Dean's List";}
elseif ($list == "grad") { $list_full = "gradlist";
$hdr = $tname . " Graduate's List"; }
//query the Names table...
$query = $modx->newQuery('Names');
$query->where(array('category:=' => $list_full));
$query->andCondition(array('termcode' => $term));
$query->sortby('lastname','ASC');
$query->sortby('firstname','ASC');
$query->sortby('middlename','ASC');
$total = $modx->getCount('Names',$query);
$totalVar = $modx->getOption('totalVar', $scriptProperties, 'total');
$modx->setPlaceholder($totalVar,$total);
//set the limit and offset; use in getCollection command...
$limit = 25; //$limit = $modx->getOption('limit',$scriptProperties,100);
$offset = $modx->getOption('offset',$scriptProperties,0);
$query->limit($limit,$offset);
$result = $modx->getCollection('Names', $query);
$output = '';
if($result)
{
foreach($result as $row)
{
$items .= $modx->getChunk("deangraditem", array("firstname"=>$row->get('firstname'), "middlename"=>$row->get('middlename'), "lastname"=>$row->get('lastname'),
"city"=>$row->get('city'), "county"=>$row->get('county'), "state"=>$row->get('state'), "which"=>$row->get('category')));
}
}
else {
return error_get_last();
}
$query = $modx->newQuery("Names");
$query->distinct();
$query->select(array($modx->getSelectColumns("Names", array("city"))));
$query->where(array("category"=>$list_full));
$query->andCondition(array("termcode"=>$term));
$query->sortby("city", "ASC");
$cities = $modx->getCollection("Names", $query);
foreach($cities as $city)
$cityOptions .= $modx->getChunk("deangradlist-menu-city", array("city"=>$city->get("city")));
$output = $modx->getChunk("deangradlist", array("items"=>$items, "hdr"=>$hdr, "regionOptions"=>$regionOptions, "cityOptions"=>$cityOptions));
}
return $output;
?>
MODX Staff