Yii2: Postgresql - how to cast fields for query

[fabioaccetta]

I have a grid view with a created_at column and I'd like to filter on it by partial match (i.e. with LIKE operator).
The created_at column is of type DATE, and the operator LIKE in postgresql is not defined for date types, so this query returns error:

SELECT * FROM my_table WHERE created_at LIKE '%-19%';

ERROR:  operator does not exist: created_at ~~ unknown

To correct this query, the date field has to be explicitly casted to text:

SELECT * FROM my_table WHERE created_at::text LIKE '%-19%';

How can I get this sql using \yii\db\[Active]Query? In my $modelSearch I have done several tests with no luck:

// TEST 1
$query->andFilterWhere(['like', 'created_at', $this->created_at]);
// the resulting sql is the same as above => error

// TEST 2
$query->andFilterWhere(['like', new \yii\db\Expression('created_at::text'), $this->created_at]);
// error given:
Undefined column: column "created_at::text" does not exist

// TEST 3
$query
    ->andFilterWhere(['created_at::text LIKE :date'])
    ->addParams([':date' => '%' . $this->created_at . '%']);
// error given:
Invalid Parameter – yii\base\InvalidParamException
Operator 'CREATED_AT::TEXT LIKE :DATE' requires two operands.

[nineinchnick]

Can you post generated query text from app logs? It seems like there's some autoquoting applied but it shouldn't be since you used yii\db\Expression.

[fabioaccetta]

Of course: here the logs of test 2 (translated in english, with some spaces for better format but keeping quotes)

Next exception 'yii\db\Exception' with message 'SQLSTATE[42703]:
Undefined column: ERROR:  column "created_at::text" does not exist

The SQL being executed was:
SELECT COUNT(*) FROM "my_table" WHERE "created_at::text" LIKE '%-19%''
in <PATH>/vendor/yiisoft/yii2/db/Schema.php:628

[nineinchnick]

Try adding parenthesis to the expression, this should disable autoquoting.

[fabioaccetta]

Try adding parenthesis to the expression, this should disable autoquoting.

So:

$expression = new \yii\db\Expression('(created_at::text)');
$query->andFilterWhere(['like', $expression, $this->created_at]);

Yes, it worked!
Many thanks