Batch Inserts in Yii2 ActiveRecord
Handling multiple rows within a single transaction operation is a common requirement when dealing with composite entities like Transaction and TransactionItems. The core question often arises: can you achieve this using ActiveRecord, or do you strictly need to drop down to raw DAO (Data Access Object) queries? The answer depends on whether you are looking at the framework's high-level convenience methods or its underlying capabilities.
In Yii2, while there isn't a single method named bulkInsert() that mirrors the database-specific INSERT INTO ... VALUES (...) syntax found in lower-level frameworks, you can still perform batch inserts using ActiveRecord components without abandoning the ORM. However, the approach differs significantly based on your specific use case and whether you are inserting data into a simple table or handling related entities with foreign keys.
For standalone tables where you simply need to insert multiple rows of TransactionItems, the most idiomatic Yii2 approach involves iterating through your collection and using the model's save method within a transaction block. While this might look like N+1 queries if not structured correctly, Yii's underlying connection abstraction often optimizes these operations efficiently when configured properly.
Consider a scenario where you have a Transaction object with a collection of TransactionItem objects. If you attempt to call $item->save() inside a loop without any transactional scope, every single insert happens individually. This creates a race condition risk if the application fails midway, and it generates excessive network overhead. To mitigate this, you wrap the entire operation in a database transaction:
$db = Yii::$app->db;
$count = 0;
foreach ($items as $item) {
$count++;
}
try {
$db->transaction(function () use ($db, $items) {
foreach ($items as $item) {
// Ensure the item is a new instance
if (!$item->getIsNewRecord()) {
throw new Exception('Cannot save existing record');
}
$item->save(false); // Save(false) skips validation for performance
}
});
} catch (\Exception $e) {
// Rollback happens automatically if an exception is thrown inside transaction()
Yii::error($e->getMessage());
}
This pattern leverages the database engine's ability to group multiple writes into a single atomic unit. The critical detail here is the use of save(false). By disabling validation checks during the batch loop, you bypass expensive model-level rules that would otherwise fire for every single item, significantly improving throughput. You must assume the data integrity is handled at the application level or via a separate bulk validation pass before entering the transaction block.
However, if your architecture requires inserting these items into a specific parent Transaction row first, the logic changes slightly. If the TransactionItems table has a foreign key referencing the Transaction ID, you must ensure the parent record exists and is active in the current session context before attempting to attach child records.
There is also a consideration for using raw SQL via DAO objects if performance becomes a bottleneck or if you need to utilize database-specific batch insert features (like MySQL's INSERT ... ON DUPLICATE KEY UPDATE or PostgreSQL's VALUES (...), (...) syntax) which ActiveRecord does not abstract directly. If your team uses Yii2, sticking to the ORM for logic and using raw SQL only for the heavy lifting of bulk insertion is often a pragmatic compromise. This allows you to maintain type safety and model events for the rest of your application while exploiting the raw power of the database driver when necessary.
Ultimately, the choice between ActiveRecord and DAO for batch operations comes down to readability versus raw performance tuning. For most applications, wrapping standard ActiveRecord saves in a transaction provides sufficient speed with minimal code complexity.