0
votes

I got a query

$query = User::find();
$query->joinWith(['profile']);
$query->andWhere(['not', ['user.id' => $this->searching_user_id]]);
$query->andWhere(new Expression('profile.photo::jsonb ? \'id\''));

and get error

SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters\n Failed to prepare SQL: SELECT COUNT(*) FROM \"user\" LEFT JOIN \"profile\" ON \"user\".\"id\" = \"profile\".\"user_id\" WHERE (\"user\".\"status\"=:qp0) AND (NOT (\"user\".\"id\"=:qp1)) AND (profile.photo::jsonb ? 'id')

so, if I run an SQL

SELECT "user".* FROM "user" LEFT JOIN "profile" ON "user"."id" = "profile"."user_id" 
WHERE ("user"."status"='active') 
  AND (NOT ("user"."id"=1)) 
  AND (profile.photo::jsonb ? 'id')

What im doing wrong?

1
the sql query you provided, does it fetch the correct records if run through mysql console or phpmyadmin. - Muhammad Omer Aslam
@MuhammadOmerAslam if you are asking - yes - Miroff

1 Answers

0
votes

I I can't test this, as I don't run postgre sql, but it looks like the last statement is not quite correct. I think that the table name and field needs to be in parenthesis. You can tell this, because Yii2 has not properly quoted the field names. Also you have mixed two sets of single-quote brackets, which php often does not like, although you have properly escaped the single quotes.. Try nesting them like this;

$query->andWhere(new Expression("(profile).photo::jsonb ? \'id\'"));

As I said I can't test this, but hope it is of some use to you