Hi
I installed PostgreSql and the Chinook database.
I stumbled on a first issue which was that the column in GROUP BY had to be quoted, I updated KeenDataProvider like this:
$pkNames = (array)$this->model->tableSchema->primaryKey;
$schema=$this->model->getDbConnection()->getSchema();
foreach($pkNames as $k=>$v)
{
$pkNames[$k] = $schema->quoteColumnName($this->model->tableAlias.'.'.$v);
}
$this->criteria->group = implode(',', $pkNames);
Then I got another issue:
]CDbCommand failed to execute the SQL statement: SQLSTATE[42803]: Grouping error: 7 ERROR: column "track.TrackId" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...UnitPrice" AS "t0_c3", "t"."Quantity" AS "t0_c4", "track"."T...
[size=2]^. The SQL statement executed was: SELECT "t"."InvoiceLineId" AS "t0_c0", "t"."InvoiceId" AS "t0_c1", "t"."TrackId" AS "t0_c2", "t"."UnitPrice" AS "t0_c3", "t"."Quantity" AS "t0_c4", "track"."TrackId" AS "t1_c0", "track"."Name" AS "t1_c1", "track"."AlbumId" AS "t1_c2", "track"."MediaTypeId" AS "t1_c3", "track"."GenreId" AS "t1_c4", "track"."Composer" AS "t1_c5", "track"."Milliseconds" AS "t1_c6", "track"."Bytes" AS "t1_c7", "track"."UnitPrice" AS "t1_c8", "album"."AlbumId" AS "t2_c0", "album"."Title" AS "t2_c1", "album"."ArtistId" AS "t2_c2", "artist"."ArtistId" AS "t3_c0", "artist"."Name" AS "t3_c1" FROM "invoiceline" "t" LEFT OUTER JOIN "track" "track" ON ("track"."TrackId"="t"."TrackId") LEFT OUTER JOIN "album" "album" ON ("album"."AlbumId"="track"."AlbumId") LEFT OUTER JOIN "artist" "artist" ON ("artist"."ArtistId"="album"."ArtistId") GROUP BY "t"."InvoiceLineId" ORDER BY "artist"."Name" DESC,"track"."Name","track"."UnitPrice" DESC LIMIT 4
So I hacked the KeenActiveDataProvider to add these columns to the GROUP BY clause (quick and dirty: explicitally naming them in KeenActiveProvider). That made this request pass, but I then ran into another issue for which I found a workaround.
This is the quick and dirty hack in KeenActiveDataProvider->_prepareKeenLoading().
$pkNames = (array)$this->model->tableSchema->primaryKey;
$schema=$this->model->getDbConnection()->getSchema();
foreach($pkNames as $k=>$v)
{
$pkNames[$k] = $schema->quoteColumnName($this->model->tableAlias.'.'.$v);
}
$pkNames[]=$schema->quoteColumnName('track.TrackId'); // Explicit column names for test.
$pkNames[]=$schema->quoteColumnName('album.AlbumId');
$pkNames[]=$schema->quoteColumnName('artist.Name');
$this->criteria->group = implode(',', $pkNames);
which got me:
Active record "Invoiceline" is trying to select an invalid column ""t"."InvoiceLineId","track"."TrackId","album"."AlbumId","artist"."Name"". Note, the column must exist in the table or be an expression with alias.
Which is Yii complaining, but I circumvented using another hack in KeenActiveDataProvider->afterFetch().
array('select'=>array_merge($this->extrakeys,CPropertyValue::ensureArray(explode(',',$this->criteria->group))),
'with'=>$keenGroup)
I still got:
Active record "Invoiceline" is trying to select an invalid column ""album"."AlbumId"". Note, the column must exist in the table or be an expression with alias.
.
Ignoring the exceptin (commenting it) in CActiveFinder, got me past by it, but I got another error entering a search term.
So currently the most effective method is to "disable" keen loading which I do like this in the demo (not uploaded at this moment:
$dataProvider=$model->search();
if($dataProvider instanceof KeenActiveDataProvider) {
list($type)=explode(':',$model->getDbConnection()->connectionString);
if($type!=="pgsql") {
// The keendataprovider does not work well for pgsql
/* @var $dataProvider KeenActiveDataProvider */
$dataProvider->withKeenLoading=array(
'track',
'track.album',
'track.album.artist',
'track.mediatype',
'invoice.customer',
'invoice',
'invoice.customer.support',
'track.genre',
);
}
}
So the issue seems to be with the KeenActiveDataProvider which would have to add the columns to the group by clause. Or maybe there is another that I do not think of at this moment. Skipping it makes the demo work for me on a PostgreSQL database. Some benefit is lost, but there is still a lot of functionnality available in RelatedSearchBehavior…