How to write a similar sql query in yii?

Question

MySQL

SELECT distinct
post.id as id,
post.authorID as authorID
FROM post left OUTER JOIN comment ON post.id = comment.postID
WHERE comment.authorID <>1;

or maybe you can somehow write it in pure sql and feed it to the dataprovider as a query instead of the current request?:

$dataProvider = new ActiveDataProvider([
                    'query' => Post::find()->where(['activateStatus' => 1])->andWhere(['closedDate' => NULL])->orderBy('rating DESC'),
                    'pagination' => [
                        'pageSize' => 20,
                    ],
                ]);

Answer 1

Goodnight.
There is a SqlDataProvider .
Here is an article on using join in ActiveRecord

Answer 2

See:
the find() method - called on an AR object returns an AR.
In your code:

$query = (new Query())->select([
                    'post.id' => 'id',
                    'post.authorID' => 'authorID',
                    'post.title' => 'title',
                    'post.content' => 'content',
                ])
                    ->distinct()
                    ->from('post')
                    ->leftJoin('comment', '`comment`.`postID` = `post`.`id`')
                    ->where(['comment.authorID' => 1])
                    ->with('comments')
                    ->all();

The error is that when you create a query through new yii\db\Query, then you have an array result type, not AR. And only AR objects have the with method, and it will only work when you have a declared getter for the requested property. In this particular case, you don't need the with() method
, you've already done the table join. As for the query, the following code will generate the following SQL:

SELECT DISTINCT 
"post"."id" AS "id", 
"post"."authorID" AS "authorID",
"post"."title" AS "title", 
"post"."content" AS "content" 
FROM "post" 
LEFT JOIN "comment" ON `comment`.`postID` = `post`.`id` 
WHERE "comment"."authorID"<>1

$query = (new Query())
            ->select([
                'id' => 'post.id',
                'authorID' => 'post.authorID',
                'title' => 'post.title',
                'content' => 'post.content',
            ])->distinct()
            ->from('post')
            ->leftJoin('comment', '`comment`.`postID` = `post`.`id`')
            ->where(['<>','comment.authorID', 1]);

You can also create custom queries like this:

Yii::$app->db->createCommand('SELECT distinct
post.id as id,
post.authorID as authorID
FROM post left OUTER JOIN comment ON post.id = comment.postID
WHERE comment.authorID <> :author_id;', [':author_id' => 1])

PS If you want to return exactly the ActiveDataProvider, then keep in mind that it is the ActiveQuery object that needs to be passed to it as a query, which, in turn, returns an AR. Therefore, it should be called by the find () method, which was discussed at the very beginning. I would suggest you use SqlDataProvider in this particular case, as slo_nik rightly said , or ArrayDataProvider, but then you will always load into memory all the objects that you have in the database, regardless of pagination. This is very costly and best avoided.