There is the following code to get data from the database via Yii

$query = Task::find() ->where([Task::tableName().'.parent_id' => 0]); $query->leftJoin(TaskModel::tableName()); $query->leftJoin(ClientEntity::tableName()); $query->andWhere([TaskModel::tableName().'.task_id' => Task::tableName().'.id']); $query->andWhere([ClientEntity::tableName().'.client_id' => TaskModel::tableName().'.model_id']); $rows = $query ->offset($pages->offset) ->limit($pages->limit) ->orderBy([ Task::tableName().'.status' => SORT_DESC, Task::tableName().'.`estimated_date`=0' => SORT_ASC, Task::tableName().'.estimated_date' => SORT_ASC ]) ->all();

It seems everything is observed, but in the end I get

 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (`task`.`parent_id`=0) AND (`task_model`.`task_id`='{{%task}}.id') AND (`c' at line 1

I tried to write the names of the tables in variables, put the operators in quotes separately, but as a result I always get the same thing.

  • In the generated query there is a condition `task . parent_id = 0`, which is missing from the code snippet shown. And in general it seems to me that you have a little missing binding condition in leftJoin ... - Akina
  • @Akina when initializing the query $query = Task::find()->where([Task::tableName().'.parent_id' => 0]); - Ingwar
  • Well, show the code for the formation of the request, not a piece. - Akina
  • @Akina added code in the topic - Ingwar
  • one
    1) Place the methods in the correct order (find..leftjoin..where). 2) Do not place binding conditions in the WHERE clause. This is not only wrong, it also turns the LEFT JOIN into an INNER JOIN (if the syntactic control passes, of course). - Akina

1 answer 1

It was necessary just to make join's normally (thanks @Akina)

Well, as it turned out later, write down the conditions for the joines in just such a format. That one line would turn out, differently 2 parameter is put in quotes, and 2 models cannot be used.

 $query = Task::find() ->where([Task::tableName().'.parent_id' => 0]); $query->leftJoin(TaskModel::tableName(), TaskModel::tableName().'.task_id = ' . Task::tableName().'.id'); $query->leftJoin(ClientEntity::tableName(), ClientEntity::tableName().'.client_id = ' . TaskModel::tableName().'.model_id']); $rows = $query ->offset($pages->offset) ->limit($pages->limit) ->orderBy([ Task::tableName().'.status' => SORT_DESC, Task::tableName().'.`estimated_date`=0' => SORT_ASC, Task::tableName().'.estimated_date' => SORT_ASC ]) ->all();