Yii - Query Manipulation for Custom CGridView with Advanced Search

Question

So I added CGridView to enable an advanced search feature tailored to my organization's needs.

• Filter - allows you to show / hide columns in the table, as well as change the order of columns by dragging the small drag icon to the left of each element.
• Sorting . Allows you to select multiple columns, specify "Ascending" or "Descent."
• Search . Select the column and paste the search options. Operators adapted to the data type of the selected column.

Version 1 works, albeit slowly. Basically, I had hands in the inner workings of CGridView , where I pull out the results from a DataProvider and do a search and sort in PHP before the contents of the table are displayed.

Now, having written Version 2, where I want to focus on clever CDbCriteria creation, allowing MySQL to do heavy lifting so that it works faster. The implementation is trivial when working with a single database table. The difficulty arises when I deal with 2 or more tables ... For example, if the user intends to search in a field that is STAT , I need this relation to be present in my query so that I can include comparisons.

That is the question. How can I assure that Yii includes all the with relationships in my query to enable comparisons? I included all my relationships with my criteria in the search function and I tried CDbCriteria together set to true ...

 public function search() { $criteria=new CDbCriteria; $criteria->compare('id', $this->id); $criteria->compare( ... ... $criteria->with = array('relation0','relation1','relation3'); $criteria->together = true; return new CActiveDataProvider( get_class($this), array( 'criteria'=>$criteria, 'pagination' => array('pageSize' => 50) ));} 

Then I will rip out the criteria from the DataProvider and add some conditions , for example, look for dates> 1234567890. But I still get such errors ...

 CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.relation3' in 'where clause'. The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`id`) FROM `table` `t` LEFT OUTER JOIN `relation_table` `relation0` ON (`t`.`id`=`relation0`.`id`) LEFT OUTER JOIN `relation_table` `relation1` ON (`t`.`id`=`relation1`.`id`) WHERE (`t`.`relation3` > 1234567890) 

Where relation0 and relation1 are BELONGS_TO relationships, but any STAT relationships depicted here as relation3 are missing. Also, why is the query a SELECT COUNT(DISTINCT 't'.'id') ?

Edit @DCoder Here is the specific relationship I'm working with right now. The main table is Call, which has HAS_MANY relation to CallSegments, which saves time. Thus, startTime Call is the minimum start time for all associated CallSegments. And startTime is a hypothetical relation3 in my anonymous request error.

 'startTime' => array(self::STAT, 'CallSegments', 'call_id', 'select' => 'min(`start_time`)'), 

Edit Other people sent me to the CDbCriteria property together , but as you can see above, I'm currently trying to no avail.

Edit It looks like the problem has : Yii and github .

Answer 1

It is not recommended to extract sql from the criteria and use it yourself.

If you use the "with" property, you can easily use comparisons, for example:

 $criteria->compare("`relation1`.`id`", $yourVarHere); 

Also, Yii does not behave very well with the group.

My STAT relationship approach uses a subquery to select Yii, followed by:

 $criteria->select = array("`t`.*", "(SELECT COUNT(*) FROM `relation3` WHERE `id` = `t`.id_relation3) AS `rel3`"); $criteria->having = "`rel3` > " . $yourValue; 

The above method creates a grid split error because the calculation is done on a different request. The workaround is to reset the “c” property and write the associations themselves in the “join” property, for example:

 $criteria->join = "LEFT OUTER JOIN `relation_table` `relation0` ON (`t`.`id`=`relation0`.`id`) LEFT OUTER JOIN `relation_table` `relation1` ON (`t`.`id`=`relation1`.`id`) LEFT OUTER JOIN `relation_table` `relation3` ON (`t`.`id`=`relation3`.`id`)"; 

Answer 2

If the error is a bit complicated to work with, you can use the stat relation as a simple HAS_ONE with:

 'select'=>'count(relation3.id)', 'joinType'=>'left join', 'group'=>'relation3.id', 'on'=>'t.id = relation3.id', 'together'=>true 

to get the value of the account for everything else?

Not sure how well this will work for your cause, but it has been useful to me from time to time.