AR vs query builder

[samdark]

AR is perfect when you need to delete, update or create one or more records sequentially. Its support of dirty attributes (saving only what was really changed) results in optimized UPDATE statements which lifts the load from database significantly and reduces chances for various conflicts connected with editing same record by multiple persons at the same time.

If you don't have really complex logic in your application and therefore it doesn't require abstracting entities, AR is the best fit for deletes, updates and creates.

AR is also OK for simple queries resulting in under 100 records per page. It's not as performant as working with arrays produced by query builder or asArray() but is more pleasure to work with.

AR is not recommended for complex queries. These are usually about aggregating or transforming data so what's returned doesn't fit AR model anyway. It is preferable to use query builder in this case.

Same goes for import and export. Better to use query builder because of high amounts of data and possibly complex queries.

[cherifGsoul]

@samdark what you think about using Query Builder that uses Active Query which returns Arrays instead of AR object?

For now I use AR for writing (Update, Delete), and query builder for fetching data.

[samdark]

I think it is good.

[cebe]

for import it may still be useful to use AR for validating imported data.

[ivansal]

Also if need to trigger AR events

[lav45]

Retrieving related data lists. As an example getCategoryList()

/**
 * @property integer $category_id
 * @property Category $category
 */
class Post  extends ActiveRecord
{
    public function rules()
    {
        return [
            [['category_id'], 'in', 'range' => array_keys($this->getCategoryList())],
        ];
    }

    public function getCategoryList()
    {
        return Category::find()
            ->select(['name'])
            ->indexBy('id')
            ->active()
            ->column();
    }

    public function getManager()
    {
        return $this->hasOne(Category::class, ['id' => 'category_id']);
    }
}