A few questions about CGridView

Hi there,

I have some obvious questions about CGridView. Even if I tried, I wasn’t able to find answers to them myself. Maybe someone find some time to help me as I’m lost here!

1. I’ve implemented mdobma’s solution with Reset button described here. But when I changed CGridView not to display filters at all, it again stopped working. I.e. Form fields are being cleaned, CGridView is being refreshed but it does not reflect changes (no, filters - revert to showing all records). I have to click Search after clicking Reset to really reset CGridView. Can’t understand why it doesn’t work - if form fields are cleaned and then CGridView receives update request?

2. I’ve modified my CGridView to respect filters with following code described in this post:

    	$criteria = new CDbCriteria;

    	$criteria->compare('DATR', $model->DATR, true);

    	$criteria->compare('BCODE', $model->BCODE, true);

    	$criteria->compare('PATIENT', $model->PATIENT, true);

    	$criteria->compare('PESEL', $model->PESEL, true);

    	$criteria->compare('UDATE', $model->UDATE, true);

    	$criteria->select = 'ID, STAT, DATR, BCODE, PATIENT, PESEL, UDATE';

    	$criteria->order = 'DATR DESC';


    	$dataProvider = new CActiveDataProvider($model, array

    	(

            	'criteria'=>$criteria,

            	'pagination'=>array('pageSize'=>20),

    	));

It seems to be half-working. For example - there are problems with case sensitivity. If I enter data lowercase and grid view contains it uppercase, it does not work.

Changing:

$criteria->compare('PATIENT', $model->PATIENT, true);

to:

$criteria->compare('PATIENT', strtoupper($model->PATIENT), true);

also does not solve problem. If I don’t enter data in uppercase, it will not be searched (filtered) - since data is uppercased in DB. Is it a normal behaviour?

3. What is the best way to search empty columns? Right now if I enter empty value to search form field, filtering for that particullar column is ignored, if I’m not mistaken.

4. How can I use CDbCriteria->compare (or any other CDbCriteria method) to have more complex searching ./ filtering? I.e. If I want to filter field (column) having date for being in some range?

Thanks in advance for any help here. Cheers!

You have many question here…

My solution for the reset button uses the filter variables, so if you don’t show the filter than it does not wrork… you would need to change the jQuery code to empty the search form input values instead of the filter input values…

For case sensitivities… I had same problem on postgres where case sensitiviti is OFF by default and instead of LIKE (default in Yii) you need to use ILIKE… so you should check with ORACLE what is the operator for case senstivity…

Using your code (slightly modified by me) is emptying search form and then is forcing CGridView refresh, it only brings no result. I don’t think it is the problem with the code you provided me, but rather with current implenation of CGridView’s jQuery refreshing code. I still can’t understand, why it is possible, that when I click Reset form is being cleaned and CGridView is being refreshed (with no result) and if I immediately click Submit with empty form (emptied be clicking Reset) again CGridView is being refreshed but this time there are results - it reverts to original state. Either I’m missing something or it seems that both Submit and Reset are calling different version of CGridView’s refresh code <-- which is of course not true.

Seems that the only solution here would be to implement another submit button, name it Reset and add a code to its onclick or to onsubmit of the form to empty all fields before submitting the form. But I’m not sure how a form with two submit buttons would work and what W3C standards has to say about such forms? I was told that any form should always have only one submit button and optionally only one reset button. Even forms having no submit button, and using some image or button for validating forms with AJAX, not true submit request falls beyond world standards, if I’m not mistaken.

Well… This may be a case, but I would be sceptical about, if this is DB related problem. If I’m uppercasing what comes from search form (am I?):

$criteria->compare('PATIENT', strtoupper($model->PATIENT), true);

and if what comes from DB is already uppercased than why searching/filtering/updating CGridView does not work? This seems to be code/implementation related problem. If both form contents and what comes from DB is uppercased and even so filter is not working properly (saying no results) then are you sure, if DB or SQL query has something to tell here?

It all depend on your jQuery function… check the search form submit code

$('.search-form form').submit(function(){

	$.fn.yiiGridView.update('category-grid', {

		data: $(this).serialize()

	});

	return false;

So in the reset function after you empty all the values call that function… or copy it’s code to your function

Problem 1: Solved! Pasted solution to original thread. If someone needs it, should look there.

Problem 2: Also solved. I found out that problem with uppercase / lowercase only appeared on non-Latin characters and this lead me to a conclusion that strtoupper should be changed to:

$criteria->compare('PATIENT', mb_strtoupper($model->PATIENT, 'UTF-8'), true);

This because original strtoupper does not uppercase non-Latin characters. Please note, that at least in my case, forcing UTF-8 encoding with second parameter is necessary. PHP documentation states that if this parameter is omitted, the internal character encoding value will be used. But in my case it turned out to be not true. Even though I have all my pages encoded in UTF-8 and my DB connection set to force UTF-8 encoding, comparison did not work until I forced encoding.

Problems 3 & 4: Currently not solved.

Note that they mean "internal" encoding of PHP… not your pages or database connection… for this you need to check php.ini

3. how would you make a SELECT to get those "empty" columns?

4. There is betweenCondition - http://www.yiiframework.com/doc/api/1.1/CDbCriteria#addBetweenCondition-detail

Or you can just do it the old fashioned way… FIELD >= start value and FIELD <=end value

But if you are asking how to do that with the CGridView… one idea would be to put two iNPUT elements for the filter…

Glad, you wrote it, because I just was intend to ask.

Search form generated by yiic is builded and parsed using model. I.e. Form fields are build like that:

<?php echo $form->labelNA($model, 'BCODE'); ?>

and they are processed like that (for example, cause can be processed in many other ways):

<?php

    	$criteria = new CDbCriteria;

    	$criteria->compare('BCODE', $model->BCODE, true);

    	$criteria->order = 'DATR DESC';


    	$dataProvider = new CActiveDataProvider($model, array

    	(

            	'criteria'=>$criteria,

            	...

    	));


    	$this->widget('zii.widgets.grid.CGridView', array

    	(

            	'id'=>'zlecenia-grid',

            	'dataProvider'=>$dataProvider,

            	...

    	));

?>

But how can I add to search form a new field that is not related to model and process it with CDbCriteria for use in CGridView AJAX update sequence? I mean - I think I have to use some helper method like CHtml::inputField() specyfing its name/id. But how to catch it in above code and process it with CDbCriteria?

Chek this thread for an idea - http://www.yiiframework.com/forum/index.php?/topic/14390-search-for-many-many-value-in-cgridview/

Basically in the model you can add a new attribute (variable) like

public rangeEnd;

And in the view/controller you use it like

$model->rangeEnd

Thanks! I was surprised that this is so easy. But, after all - this is Yii! ;]

Two more, hopefully last, questions in this subject:

1. If am feeding CGridView with CActiveDataProvider and that provider with CDbCriteria, then can I modify at some stage SQL that is being generated and used by CGridView for showing selected, sorted and paginated results? If yes, then where and how? Or do I have to change CActiveDataProvider to CSqlDataProvider, manually edit CSqlDataProvider.sql and feed it to CGridView? But then I would have to resign from using CDbCriteria and have to write whole SQL myself, right?

2. Where exactly sorting of CGridView is done in any basic application generated by yiic webapp? I’ve been modifying my CGridView strongly for past few days and today I discovered that I lost sorting during that. I.e. clicking on columns is possible, arrows showing column and order of sort are shown, CGridView is being updated (AJAX circle rounds to say it this way), but actual data in CGridView is not changed - i.e. no actual sorting takes place.

I searched another application, freshly out-of-the box generated by yiic, but can’t figure out, what difference between CGridView in that app and in my main project makes sorting working in first and not working in second. I can’t find line(s) of code responsible for sorting CGridView.

Cheers.

1. did not understand your need, maybe if you explain what, where and why you want to change… .and why that change you cannot make with CDbCriteria…

2. It’s possible that you are givin an ORDER BY to a criteria and that’s why you always get the same order

In particular: My date column contains date already formatted (by DB) to a string format like “2010-02-23 08:43:22”. Since I don’t want to compare this as a string only as date-time, I need to surround SQL WHERE part around date_column with TODATE(’’) - i.e. TODATE(‘value_from_date_column_passed_as_string’). Because in Oracle this is the way, you can convert string representation of date to an actual date-format on the fly in SQL query.

In general: wanted to know if there is any way of modifying SQL for CActiveDataProvider for future references? Or, when sql modification is needed, if CSqlDataProvider must be used?

Damn, I had to ask you, to get answer that I’m a moron! :]

But, on the other hand. If I’m using solution like that:

<?php

    	$criteria = new CDbCriteria;

    	$criteria->compare('DATR', $model->DATR, true);

    	$criteria->compare('BCODE', $model->BCODE, true);

    	$criteria->select = 'DATR, BCODE';

//    	$criteria->order = 'DATR DESC';


    	$dataProvider = new CActiveDataProvider($model, array

    	(

            	'criteria'=>$criteria,

            	'pagination'=>array('pageSize'=>20),

    	));


    	$this->widget('zii.widgets.grid.CGridView', array

    	(

            	'id'=>'zlecenia-grid',

            	'dataProvider'=>$dataProvider,

       		...

            	),

    	));

?>

Is there anyway to setup initial ordering of CGridView, used when page loads? Because I used that damn $criteria->order = ‘DATR DESC’; right for this purpouse - to force initial sorting of CGridView, only then I forgot I have it and was wondering, why sorting does not work?

For a default order you ca use the ‘sort’ property of CActiveDataProvider like

$dataProvider = new CActiveDataProvider($model, array

   ...

   'sort'=>array(

      'defaultOrder'=>'DATR DESC',

   ),

  ...

Thanks (+1)! :] And sorry, for asking before figuring it out myself from documentation! :[

No problem, and thanks… …

the default sorting was my first problem I asked in the forum - http://www.yiiframew…hp?/topic/7482-

witch reminds me of another topic - http://www.yiiframew…p?/topic/12334-

So it’s better to use

'defaultOrder'=>array('DATR'=>true),

so that a proper icon is displayed in the CGridView

As for the other problem…

Try to use something like:

$criteria->compare('TODATE(datecolumn)',$this->date);

Anyway after you create the criteria you can always acces the $criteria->condition and process it as you need before giving it to CActiveDataProvider

Thanks, again! Any of above solution should be fine for me!

And another +1 for solving my another problem! :]

Echoing $criteria->condition out to see what is current condition, gives me SQL before binding actual values, that is:

((PATIENT NOT LIKE :ycp0) AND (PESEL LIKE :ycp1))

Any way to see it with values (data) binded (just for testing purpose)? Or to see whole SQL sent to CActiveDataProvider / CGridView, not just WHERE clause?

I’m afraid not this way… because the actual binding is done by CDbCommand->bindParam() that in turn uses proper PDO driver to do that…

You can see the actual SQL command executed by enabling the CWebLogRoute logging…

But how can I do it? I must be doing something wrong. I already had (generated by yiic):

'log'=>array

(

    	'class'=>'CLogRouter',

    	'routes'=>array

    	(

            	//Logging errors and warnings to a file in protected/runtime directory

            	array

            	(

                    	'class'=>'CFileLogRoute',

                    	'levels'=>'error, warning',

            	),


            	//Show log messages on web pages

            	array('class'=>'CWebLogRoute'),

    	),

),

But I see only error messages on my webpages, even if default configuration of CWebLogRoute.levels is empty, showing all kind of logs (according to documentation). I changed it to:

//Show log messages on web pages

array

(

    	'class'=>'CWebLogRoute',

    	'showInFireBug'=>true,

),

But then again, I see only AJAX calls logs in Firebug’s console. I have it empty for a standard requests (i.e. switching between pages) and I don’t see even a piece of SQL neither on webpage nor in console! :[

You should see the SELECT commands too, but without the parameter values… for that you need to add to the ‘db’ component

'enableParamLogging'=>true,

but you cannot see the SQL executed by the AJAX call.