creating sql from criteria

Yii 1.1 General Discussion
1

Here is what I am doing:




$model=new Leads('search');

		$model->unsetAttributes();	

		if(isset($_GET['Leads']))

			$model->attributes=$_GET['Leads'];

        

        if(isset($_GET['download'])) {

            $this->download($model);

            return;

        }

In that $this->download() method Im creating a csv file. Problem is its a few hundred records and the framework always runs out of memory due to attached behaviors etc.

What I would like to do, is get that criteria created for the CActiveDataProvider and some how create a sql statement that I can just pass to DAO instead. I’ve gone through the source and have seen how it uses command builder to eventually create a statement, then it has to populate an array due to all the t2_* type aliases.

Has someone figure out this problem before? I guess I’m trying to avoid writing too much code with escaping column names and building a huge statement etc, not really sure how to best get around that.

2

Maybe barking up the wrong tree but how are you building the CSV? Line by line or one big query?

I had a situation where I was looping over a bunch of data and inserting using Active Record and it would time out. I changed my method to build the array of insert data and did a multiline insert instead and its >10x faster.

In general, use AR first and switch to DAO queries if necessary due to speed. Also look at CDbCommand::getText() to get the raw SQL.

3

I actually wrote an extension that makes it easy to export csv from an array or cactivedata/sql provider. I think I could probably figure out how to get the sql now that you pointed out the getText method, but I would still have to populate the array with the attributes somehow. I’ll see what I can find out, thanks.

4

this seems to work ok, but relationships just come out as ids and not the stuff I want, like campaign.name and not just the campaign_id. at least is is a start.




$criteria = $this->dataProvider->getCriteria();

                $model = $this->dataProvider->model;

                $criteria = $model->getCommandBuilder()

                                  ->createCriteria($criteria,array());

                $command = $model->getCommandBuilder()

                                 ->createFindCommand($model->getTableSchema(), 

                                                     $criteria);

                #echo $command->getText();

                #exit;

                $data = $command->queryAll();