writing a custom query in symfony

//it is javascrit file need to include at run time in ajax
$this->response = $this->getResponse();
$this->response->addJavascript(‘/sf/prototype/js/prototype’);
$this->response->addJavascript(‘/sf/prototype/js/effects’);
$this->response->addJavascript(‘/sf/prototype/js/controls’);
//end  javascript of file need to include at run time in ajax

$selSearch = $this->getRequestParameter(‘selSearch’);
$txtSearch = $this->getRequestParameter(‘txtSearch’);
$extrVar =’&mainpg=1′;
if($selSearch) $extrVar .=’&selSearch=’.$selSearch;
if($txtSearch) $extrVar .=’&txtSearch=’.$txtSearch;

$userId = $this->getUser()->getAttribute(‘userId’);
$this->currentUserId = $userId;

$objUser = UserPeer::retrieveByPK($userId);
$countryCode = $objUser->getUsrCountry();

//$endodedURL = $this->getRequestParameter(‘endodedURL’);

//$this->endodedURL = $endodedURL;

//here replacing the following special character – , _ , + , * , / , #
$txtSearchwithSpace = preg_replace(“[%]”,’\%’,addslashes(preg_replace(“@[-_+*/#]@”,” “,$txtSearch)));
$txtSearchwithoutSpace = preg_replace(“[%]”,’\%’,addslashes(preg_replace(“@[-_+*/#]@”,””,$txtSearch)));
$txtSearch = addslashes($txtSearch);
$txtSearch = preg_replace(“[%]”,’\%’,preg_replace(“[_]”,’\_’,$txtSearch));

$objQtzReagent = new qtzReagent();

$sqlQuery = “”;
$sqlQuery = “SELECT SQL_CALC_FOUND_ROWS “.ReagentsPeer::REA_NAME.” AS rea_name_old, “.ReagentsPeer::REA_VENDORID.” AS rea_vendorid, “.ReagentsPeer::REA_CATALOGUEID.” AS rea_catalogueid, “.ReagentsPeer::REA_CATALOGUEID.” AS rea_catalogueid_R, IF( “.ReagentsPeer::REA_USRID.” = ‘”.$this->currentUserId.”‘,’yes’,’no’) AS reagent_own, “.ReagentsPeer::REA_VENDORID.” AS rea_vendorid_R, “.ReagentsPeer::REA_ID .” AS rea_id_old, “.ReagentsPeer::REA_VENDORID.” AS rea_vendorid, “.ReagentsPeer::REA_USRID.” AS rea_usrid, “.ReagentsPeer::REA_TYPEID.” AS rea_typeid, “.ReagentsPeer::REA_NOOFCOMMENTS.” AS rea_no_comments, “.ReagentsPeer::REA_AVGRATING.” AS rea_avgrating, count(*) AS reaCount, (SELECT COUNT( “.ReagentRatingsPeer::RER_ID.” ) FROM “.ReagentRatingsPeer::TABLE_NAME.” WHERE “.ReagentRatingsPeer::RER_VENDORID.” = rea_vendorid_R AND “.ReagentRatingsPeer::RER_CATALOGUEID.” = rea_catalogueid_R ) AS ratingCount, ( SELECT “.ReagentsPeer::REA_NAME.” FROM “.ReagentsPeer::TABLE_NAME.” WHERE “.ReagentsPeer::REA_VENDORID.” = rea_vendorid_R AND “.ReagentsPeer::REA_CATALOGUEID.” = rea_catalogueid_R AND “.ReagentsPeer::REA_PRIVATE.” = ‘0’ GROUP BY “.ReagentsPeer::REA_NAME.” ORDER BY COUNT(*) DESC, “.ReagentsPeer::REA_NAME.” ASC, reagent_own DESC LIMIT 1 ) AS rea_name, ( SELECT “.ReagentsPeer::REA_ID.” FROM “.ReagentsPeer::TABLE_NAME.” WHERE “.ReagentsPeer::REA_VENDORID.” = rea_vendorid_R AND “.ReagentsPeer::REA_CATALOGUEID.” = rea_catalogueid_R AND “.ReagentsPeer::REA_PRIVATE.” = ‘0’ GROUP BY “.ReagentsPeer::REA_NAME.” ORDER BY COUNT(*) DESC, “.ReagentsPeer::REA_NAME.” ASC, reagent_own DESC LIMIT 1 ) AS rea_id FROM “.ReagentsPeer::TABLE_NAME.” LEFT JOIN “.UserPeer::TABLE_NAME.”  ON (“.ReagentsPeer::REA_USRID.” = “.UserPeer::USR_ID.”)  WHERE 1 “;
$sqlQuery .= ” AND “.ReagentsPeer::REA_ENABLE_SELFRATINGCOMMENT.” = ‘1’ AND “.UserPeer::USR_COUNTRY.” = ‘”.$countryCode.”‘  AND “.ReagentsPeer::REA_PRIVATE.”=’0′ AND “.ReagentsPeer::REA_PRODUCTTYPE.”=’nonvendor’ AND “.ReagentsPeer::REA_AVGRATING.”<>” AND (“.ReagentsPeer::REA_NAME.” LIKE ‘%”.$txtSearch.”%’ OR “.ReagentsPeer::REA_NAME.” LIKE ‘%”.$txtSearchwithSpace.”%’ OR “.ReagentsPeer::REA_NAME.” LIKE ‘%”.$txtSearchwithoutSpace.”%’)  GROUP BY “.ReagentsPeer::REA_VENDORID.”, “.ReagentsPeer::REA_CATALOGUEID.’ ORDER BY ‘.ReagentsPeer::REA_AVGRATING.” DESC, ratingCount DESC ” ;

/* —————– FOR START SETTING THE CONNECTION OBJECT ——————- */
$con = sfContext::getInstance()->getDatabaseConnection(‘propel’);
$records = sfConfig::get(‘app_paging_perpagelist’);

$curPage = $this->getRequestParameter(‘pageNv’, 1);
$limit = $records;
//$limit = 2;

$startLimit = ($curPage – 1) * $limit;

$sqlQuery .= ” LIMIT “. $startLimit.”, “.$limit;

//echo $sqlQuery;

$objRslt = $con->executeQuery($sqlQuery);

$this->objRslt = $objRslt;

$strCountQuery = “SELECT FOUND_ROWS() as totRec”;
$rsCnt = $con->executeQuery($strCountQuery);
$rsCnt->next();
$this->totRec = $rsCnt->get(‘totRec’);

$pager = myGeneric::getCustomPagerArray($curPage, $limit, $this->totRec);

$this->pager = $pager;
$this->objRslt = $objRslt;
$this->currentPage = $curPage;

if ($curPage && $this->endodedURL) {
$tmpURL = base64_decode($this->endodedURL);
$tmpURL = unserialize($tmpURL);
//print_r(‘<pre>’);
//print_r($tmpURL);
//print_r(‘</pre>’);
$tmpURL[‘pageNv’]=$curPage;
$tmpURL[‘pageV’]=$this->getRequestParameter(‘pageV’, 1);
$this->endodedURL = base64_encode(serialize($tmpURL));
}
if($this->endodedURL) $extrVar .=’&endodedURL=’.$this->endodedURL;

//print_r($pager);
$this->objQtzReagent = $objQtzReagent;

$this->extrVar = $extrVar;