Which is better: yii\db\Query or Active Record?

Question

Yii

There is a query (working one) created according to the tutorial using the yii\db\Query query builder:

$data = (new \yii\db\Query())
    ->select(['page.name', 'page_category.name', 'page_value.value', 'page_value.position', 'page_params.type','page_params.variants'])
    ->from('page')
    ->where(['page.page_id' => 1])
    ->leftJoin('page_category', 'page.cat_id = page_category.cat_id')
    ->leftJoin('page_value', 'page.page_id = page_value.page_id')
    ->leftJoin('page_params', 'page_value.param_id = page_params.param_id')
    ->all();

Do you think it's better to use it in this form, or remake it in Active Record and use existing models? If so, how to correctly compose a query in Active Record?

Answer 1

There is no winner in the Query VS ActiveRecord battle - they are tools for solving different problems.
But I want to digress from your question and ask if foreign keys are configured in your database? If not, then I advise you to start learning from this moment, understanding foreign keys in MySQL.
After that, you need to read the official documentation on the ActiveRecord topic very thoughtfully and well ( www.yiiframework.com/doc-2.0/guide-db-active-recor...
After that, you should have a more complete picture of how this to use together and what it is responsible
for.Now let's get back to using Query and AR, well, everything is simple here, I will just give a code example

/**
 * Class VehicleQuery
 * @package frontend\modules\v1\models\vehicle
 */
class VehicleQuery extends ActiveQuery
{
    public $type;

    /**
     * @param \yii\db\QueryBuilder $builder
     * @return \yii\db\Query
     */
    public function prepare($builder)
    {
        if ($this->type !== null) {
            $this->andWhere([Vehicle::tableName() . '.vehicle_type' => $this->type]);
        }
        return parent::prepare($builder);
    }

    /**
     * Returns popular and active vehicles based on vehicle type
     * @return $this
     */
    public function popular()
    {
        $this->andWhere([
            Vehicle::tableName() . '.promoted' => 1,
            Vehicle::tableName() . '.deleted' => 0,
            Vehicle::tableName() . '.status' => '1',
            Vehicle::tableName() . '.in_use' => 1
        ]);
        return $this;
    }

    /**
     * @return $this
     */
    public function active()
    {
        $this->andWhere([
            Vehicle::tableName() . '.status' => '1',
            Vehicle::tableName() . '.deleted' => 0
        ]);
        return $this;
    }

    /**
     * @return $this
     */
    public function inUse()
    {
        $this->andWhere([Vehicle::tableName() . '.in_use' => 1]);
        return $this;
    }


    /**
     * Returns most rated vehicles
     * @return $this
     */
    public function rated()
    {
        $this->addOrderBy([
            Vehicle::tableName() . '.rating' => SORT_DESC,
            Vehicle::tableName() . '.created_at' => SORT_DESC
        ]);
        return $this;
    }

    /**
     * Returns most recently vehicles
     * @return $this
     */
    public function recently()
    {
        $this->addOrderBy([
            Vehicle::tableName() . '.created_at' => SORT_DESC,
            Vehicle::tableName() . '.rating' => SORT_DESC
        ]);
        return $this;
    }
}

We have a Vehicle model and to work with this model we need to build queries, this is exactly what the VehicleQuery class does - it takes on this responsibility. It is very important that the Query class be responsible only for building queries, the methods should not return find or save, they should only return an instance of Query.
I will give the code of the Vehicle model

class Vehicle extends ActiveRecord
{
    public static function find()
    {
        return new VehicleQuery(get_called_class());
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getCarBrand()
    {
        return $this->hasOne(CarBrand::className(), ['id' => 'car_brand_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getCarGeneration()
    {
        return $this->hasOne(CarGeneration::className(), ['id' => 'car_generation_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getLanguage()
    {
        return $this->hasOne(Language::className(), ['id' => 'language_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getUser()
    {
        return $this->hasOne(User::className(), ['id' => 'user_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getPosts()
    {
        return $this->hasMany(Post::className(), ['car_id' => 'id']);
    }
}

The model describes the entity, rules, relations, attributes - this is actually the main responsibility of the model, it is not recommended to implement any additional logic in the model.
From the above, your query might look like this:
This query will select the most recently active vehicles with getUser(owner) and getPosts(blogs) links.

Answer 2

Do you think it's better to use it in this form, or remake it in Active Record and use existing models?

What do you think, is it better to have normal linked data structures, or randomly dumped into an array?

If so, how to correctly compose a query in Active Record?

You need to read the documentation and write the code.

Answer 3

If you set up links with page_category, page_value and page_params, you can get the following request:

$data = Page::find()
    ->where(['page.page_id' => 1])
    ->with('page_category')
    ->with('page_value')
    ->with('page_params')
    ->all();

Answer 4

First of all, you need to set up table links in the database (set keys) - this is done using the MySQL manager (or whatever database you have there).
Next, generate object classes (in Yii terminology - models) using Gii (so as not to write code manually). If you did point 1 correctly, then it will generate all the necessary links for you (after which it makes sense to delete unnecessary ones and rename, for example, the pageValue link to value - but this is optional).
After that, just use ActiveRecord, for example:

$page = Page::findOne(1);
echo $page->value->text;
echo $page->category->title;

foreach($page->params as $param){
   echo $param->name .' : '. $param->value;
}

Yii itself will execute the necessary requests and load the necessary data.
The with method given by colleagues allows to reduce the number of requests (loads links at once). joinWith allows you to join a table in a query to make conditions on it, and there are many other things in ActiveRecord - all this is described in the documentation.
All in all - ActiveRecord is an amazingly convenient mechanism, it is definitely worth exploring.