CActiveRecord with Gridview and Relations

Hello,

I have an active record using the magic getProperty() function to create a custom column.

I.E.

getMyColumn(){

  return $this->last_30/$this->total;

}

I got this from Here.

Now when I add criteria to my dataprovider I attempt to add a condition on this new column.

I.E.

$criteria->addBetweenCondition('myColumn', 1, 2);

The page breaks with the following error:

CDbException

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘myColumn’ in ‘where clause’.

I believe it errors on this because it is first calling a count sql call to provide this to the pager.

How do I fix this? I need a count to my pager but I need it to use the correct criteria which is on a custom column.

Criteira is for finding records, and it communicates "directly" with the database, the error is right… there is no such column

what are you trying to accomplish here? get the records that are what?

As an example:

Say I have a table that has two columns times_downloaded, times_viewed.

Now I created a custom column that gave the conversion rate. i.e. times_downloaded/times_viewed.

Next, I want to query my data and find all the pages that had a conversion rate above 50%.

i.e. $criteria->comapare(‘myCustomColumn_conversionRate’, '>: 50%).

This is what I’m trying to accomplish. I do not want an extra column that stores this conversion rate because it is redundant – I can extract it from the two columns.

Something like this might work (untested)

$criteria->select = "otherstuff..., times_downloaded/times_viewed AS percent";

$criteria->addCondition("percent >= 50");

The issue is the initial COUNT(*) call being done to load the count value for the pager.

I can try your suggestion, but I believe it is still going to crash before it even gets through.

Sorry it is exactly:

SELECT COUNT(DISTINCT t.id)

Yes, even after setting the select property it crashed before it gets there, following the stack trace, the problem is:

CDataProvider.php(184): CActiveDataProvider->calculateTotalItemCount()

Ok, I faked the totalItemCount by hardcoding it to a number in the CActiveDataProvider constructor.

i.e. ‘totalItemCount’ =>‘10’,

Now I am getting the following error:

Active record "myRecord" is trying to select an invalid column "myCustomColumn". Note, the column must exist in the table or be an expression with alias.

I added to the select statement like suggested:

$criteria->select = ‘myCustomColumn’;

I was able to fix my issue.

My problem was I was using the alias in the where condition when I should of been using the logic…silly me.

The suggested:

$criteria->select = "otherstuff..., times_downloaded/times_viewed AS percent";

$criteria->addCondition("percent >= 50");

should be:

//$criteria->select = "otherstuff..., times_downloaded/times_viewed AS percent"; //Not needed

$criteria->addCondition(" (times_downloaded/times_viewed) >= 50");

Im glad it worked, but I believe:

$criteria->select = "otherstuff..., times_downloaded/times_viewed AS percent";

$criteria->addCondition("percent >= 50");

should work too