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

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


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=""]][[/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.


public function executeIndex(sfWebRequest $request)
$this->pager = new sfDoctrinePager('Wine', '100');
$this->pager->setPage($request->getParameter('page', 1));
$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;



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');

if($this->export == 'Y') {

if($this->query!='') //Use Sphinx for full text search
$this->query = ($this->query!='')?implode('* ', explode(' ', $this->query.'*')):'';

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


//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 );

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)


Rodrigo Prado de Jesus answers:


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:



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!