Ask your Symfony questions! Pay money and get answers fast! (more info)

Where do I get the raw SQL of a sfSphinx query? Symfony

  • SOLVED

I'm working with Doctrine and Symfony 1.4.

Using sfSphinx for search, but can not find a way to get the SQL. Need the raw SQL. Looked at the interface here:

[[LINK href="http://trac.symfony-project.org/browser/plugins/sfSphinxPlugin/trunk/lib/sfSphinxClient.class.php?rev=22670"]]http://trac.symfony-project.org/browser/plugins/sfSphinxPlugin/trunk/lib/sfSphinxClient.class.php?rev=22670[[/LINK]]

I've built a search interface that works fairly well. Sometimes the user needs a lot of data, but the results, in the web browser, are paginated, so I never run into out-of-memory errors.

However, sometimes the user needs to export that data. And sometimes the data is 2 gigs. So staying with the normal Symfony/Doctrine framework leads me into out-of-memory errors.

I've decided for the exports what would probably work would be to handle this with a cron script. I will store the raw SQL of the query in a database table somewhere, and then the cron script will look up the SQL, run the query, and write the results to a disk.

But where do I get the raw SQL? What Doctrine method gives me this?

Using Sphinx for the search code.

Code:

public function executeIndex(sfWebRequest $request)
{
/*
$this->pager = new sfDoctrinePager('Wine', '100');
$this->pager->setQuery(Doctrine_Core::getTable('Wine')->createQuery('a')->orderBy('vintage'));
$this->pager->setPage($request->getParameter('page', 1));
$this->pager->init();
*/
$this->pager=false;
$this->form = new WineForm();

$this->form->bind($request->getParameter($this->form->getName()), $request->getFiles($this->form->getName()));

$this->query = $this->getRequestParameter('q');
$this->page = $this->getRequestParameter('page', 1);
$this->toShowInSearchForm = $this->filters;

}





[UPDATE]

Sorry, I am an idiot. I just realized the real work was being done in this block, calling Sphinx search code (called via Ajax):

public function executeSearch(sfWebRequest $request)
{
$this->query = $this->getRequestParameter('q');
$this->page = $this->getRequestParameter('page', 1);
$this->export = $this->getRequestParameter('export', 'N');
$valuesToFilter=$request->getParameter('wine');
$configuration=array();
$configuration['pageSize']=50;

if($this->export == 'Y') {
$configuration['pageSize']=200;
}
//$configuration['orderBy']='name';

if($this->query!='') //Use Sphinx for full text search
{
$this->query = ($this->query!='')?implode('* ', explode(' ', $this->query.'*')):'';
$configuration['sphinxIndexName']='msc_winesearch_index';
}

$gSearch= new GeneralSearch('Wine', $configuration);

//die(var_dump($valuesToFilter));
$gSearch->setFilters($this->filters);

//Convert dates for Sphinx search to integers
/*
$valuesToFilter['created_at']['from_s'] = strtotime($valuesToFilter['created_at']['from']);
$valuesToFilter['created_at']['to_s'] = strtotime($valuesToFilter['created_at']['to']);
*/

if ($request->isXmlHttpRequest()) {
$this->pager=$gSearch->search($this->query, $this->page, $valuesToFilter );
$res=$this->pager->getResults();

if (!$res) {
return $this->renderText('No results for '.$this->query);
}

return $this->renderPartial('wine/search_results', array('pager' => $this->pager));
}

return $this->executeDownload($gSearch, $this->query, $valuesToFilter);
}

Answers (1)

2011-02-27

Rodrigo Prado de Jesus answers:


Hi

You must use the function getSqlQuery() for your query object in Doctrine;

Look a example here:

$q = Doctrine_Query::create()
->from('User u')
->leftJoin('u.Phonenumbers p');

echo $q->getSqlQuery();

See more information here:

http://www.doctrine-project.org/documentation/manual/1_1/hu/dql-doctrine-query-language

Regards,

Rodrigo


Lawrence Krubner comments:

I'm sorry I was unclear. I think I'm really asking more about Sphinx than Doctrine. I've read through the code in the sfSphinx plugin and I was unable to find a place where I could get the SQL.


Lawrence Krubner comments:

Ah, found it. It was buried in some code written by my co-worker. Thanks!