CDbCommand and CDbCriteria or CDbCommandBuilder

Archives Yii 1.0.x Discussions
1

Hi All,

Up to this point I've been mainly using Yii's fantastic ActiveRecord features for all of my database querying and manipulation needs. I'm working on something now, however, that requires more complex queries and so have started using CDbConnection and CDbCommand to perform my queries. For example, as a very basic example, I'm simply doing something like this:



$connection = Yii::app()->db;


$connection->active = true;


$command = $connection->createCommand($sql);


$rows = $command->queryAll();

The queries I am running are for "reports" made against data tables. While the sql for each report can vary, the same basic requirements exist for each: collect filter and criteria data from the user, build an sql statement, execute the query, return results to view for output in tabular form.

To make my job easier, I'm attempting to create a generic Report class (which extends CModel) that contains all of the core logic for building the report sql, including filters and sorts, and returning the results for display in the view. That way, instead of writing the same code over and over in each action of my controller, I simply need to instantiate a Report object, pass in filter options, and ask for the results back. What I would like to do could look something like this:



// instantiate new report object


$report = new Report();


// pass in base sql without criteria


$report->setSql($sql);


// create criteria object based on user input


$criteria = new CDbCriteria;


$criteria->order = 'field1';


$criteria->condition = "$attribute = :fvalue";


$criteria->params = array(":fvalue"=>"$filtervalue";


// get resultset based on criteria


$rows = $report->findAll($criteria);

The actual CDbConnection and CDbCommand would then be instantiated and used inside of the Report object. However, from what I can tell, neither CDbCommand or CDbConnection use CDbCriteria. It seems that the only way to apply some sort of filter or criteria to the sql is to manually concatenate the sql string and then use bindParam to replace placeholders with actual values.

Is there a way to take advantage of CDbCriteria when using CDbCommand? I've also seen the CDbCommandBuilder class that looks like it could also do what I want, but again, I don't see how CDbCommandBuilder is or can be used with CDbCommand. Any ideas or suggestions? Thanks in advance.

2

You can use the following code:



$schema=Yii::app()->db->schema;


$builder=$schema->commandBuilder;


$command=$builder->createFindCommand($schema->getTable($tableName), $criteria);
3

Thanks, Qiang. You are always so prompt and helpful! Since my query uses more than one table, I could do something like this:



$schema=Yii::app()->db->schema;


$builder=$schema->commandBuilder;


$sql = $builder->applyCondition($sql,"field1=$value1");


$sql = $builder->applyCondition($sql,"field2=$value2");


$sql = $builder->applyOrder($sql,'field1');


$command = $builder->createSqlCommand($sql,$params);


$rows = $command->queryAll();

Does that look viable and correct?

4

Nope, this won't work. applyCondition() is very simple. It simply appends a WHERE clause, and that's why your code won't work.

5

Thanks – yeah, I figured that out when I started implementing the idea. In my base Report class I ended up doing something like this to handle applying multiple conditions seperately:



class Report extends CModel


{


    protected $_conditions;


.....


    public function addCondition($conditionSql)


    {


        $this->_conditions[] = $conditionSql;


    }





    public function applyConditions()


    {


        if(is_array($this->_conditions))


        {


            $conditionSql = '';


            foreach($this->_conditions as $condition)


            {


                $conditionSql .= ' '.$condition;


            }


            if($conditionSql != '')


                $conditionSql = '1=1'.$conditionSql;


                


            $sql = $this->_reportBuilder->applyCondition($this->_reportSql,$conditionSql);


            $this->setSql($sql);


        }


    }


.....


}