Introduction

Hello to all habrovchanam, fans of Yii and Node.js.
This is the second article about the Jii framework ( GitHub ), in a previous article we looked at Data Access Objects and Query Builder.
As promised, in this article I will talk about using Active Record.

Active record

Active Record provides an object-oriented interface for accessing and manipulating data stored in databases. The Active Record class is associated with a database table, an instance of the class corresponds to a row of this table, and the attributes represent the values ​​of a specific column in that row. Instead of writing SQL queries explicitly, you have access to Active Record attributes and methods that manipulate data.

Suppose we have the Active Record class ʻapp.models.Customer`, which is associated with the `customer` table and` name` is the name of the column in the `customer` table. You can write the following code to add a new row to the `customer` table:
')

var customer = new app.models.Customer(); customer.name = 'Vladimir'; customer.save(); 

This is equivalent to the following code, in which you can make more mistakes when writing and where there may be incompatibilities with different types of data:

 db.createCommand('INSERT INTO `customer` (`name`) VALUES (:name)', { ':name': 'Vladimir', }).execute(); 

Active Record Class Declaration

First, declare the class Active Record, inheriting Jii.sql.ActiveRecord . Each Active Record class is associated with a database table, so in this class you must override the static Jii.sql.ActiveRecord.tableName () method to specify which table the class is associated with.

In the following example, we declare a class named ʻapp.models.Customer` for the `customer` table.

 var Jii = require('jii'); /** * @class app.models.Customer * @extends Jii.sql.ActiveRecord */ Jii.defineClass('app.models.Customer', /** @lends app.models.Customer.prototype */{ __extends: Jii.sql.ActiveRecord, __static: /** @lends app.models.Customer */{ tableName: function() { return 'customer'; } } }); 

The Active Record class is a model, so usually we put the Active Record classes in the `models` namespace.

The Jii.sql.ActiveRecord class inherits Jii.base.Model , which means that it inherits * all * model capabilities, such as attributes, validation rules, data serialization, etc.

Creating a database connection

By default, Active Record uses the application component `db`, which contains an instance of Jii.sql.BaseConnection to read and modify data in the database. As described in the previous article , in the “Database Access Objects” section, you can configure the application component `db` as follows:

 return { components: { db: { className: 'Jii.sql.mysql.Connection', host: '127.0.0.1', database: 'testdb', username: 'demo', password: 'demo', charset: 'utf8', } } }; 

If you want to use another connection to the database, you must override the Jii.sql.ActiveRecord.getDb () method:

 /** * @class app.models.Customer * @extends Jii.sql.ActiveRecord */ Jii.defineClass('app.models.Customer', /** @lends app.models.Customer.prototype */{ __extends: Jii.sql.ActiveRecord, __static: /** @lends app.models.Customer */{ STATUS_INACTIVE: 0, STATUS_ACTIVE: 1, // ... getDb: function() { // use the "db2" application component return Jii.app.db2; } } }); 

Data retrieval

After declaring the class Active Record, you can use it to query data from the corresponding database table.
To do this, you must perform three steps:

1. Create a new request object by calling the Jii.sql.ActiveRecord.find () method;
2. Generate the request object by calling the query creation methods;
3. Call one of the query methods to retrieve data in the form of Active Record records.

These actions are very similar to actions when working with the query designer. The only difference is that to create a query object, you must call the Jii.sql.ActiveRecord.find () method, and not create an instance using `new`.

Consider a few examples showing how to use Active Query to get data:

 //    ID 123 // SELECT * FROM `customer` WHERE `id` = 123 app.models.Customer.find() .where({id: 123}) .one() .then(function(customer) { // ... }); //    ,   ID // SELECT * FROM `customer` WHERE `status` = 1 ORDER BY `id` app.models.Customer.find() .where({status: app.models.Customer.STATUS_ACTIVE}) .orderBy('id') .all() .then(function(customers) { // ... }); //     // SELECT COUNT(*) FROM `customer` WHERE `status` = 1 app.models.Customer.find() .where({status': app.models.Customer.STATUS_ACTIVE}) .count() .then(function(count) { // ... }); //      ,    ID  // SELECT * FROM `customer` app.models.Customer.find() .indexBy('id') .all() .then(function(customers) { // ... }); 

To simplify obtaining models by ID methods are created:

• Jii.sql.ActiveRecord.findOne () : Returns the Active Record instance corresponding to the first line of the result of the query.
• Jii.sql.ActiveRecord.findAll () : Returns an array or object of several Active Record corresponding to the rows of the query result.

Both methods take the first argument in the following format:

• Scalar value: A value is treated as the value of the primary key that is being searched. The primary key is determined automatically from the database schema.
• Array of scalar values: an array is considered as the primary key values ​​that are searched. - An object whose keys are the names of the columns, and the values ​​correspond to the values ​​of the columns being searched.

The following examples show how these methods can be used:

 //    ID 123 // SELECT * FROM `customer` WHERE `id` = 123 app.models.Customer .findOne(123) .then(function(customer) { // ... }); //   ID 100, 101, 123  124 // SELECT * FROM `customer` WHERE `id` IN (100, 101, 123, 124) app.models.Customer .findAll([100, 101, 123, 124]) .then(function(customers) { // ... }); //     ID 123 // SELECT * FROM `customer` WHERE `id` = 123 AND `status` = 1 app.models.Customer .findOne({ id: 123, status: app.models.Customer.STATUS_ACTIVE }) .then(function(customer) { // ... }); //     // SELECT * FROM `customer` WHERE `status` = 0 app.models.Customer .findAll({ status: app.models.Customer.STATUS_INACTIVE }) .then(function(customers) { // ... }); 

Note: Neither Jii.sql.ActiveRecord.findOne () nor Jii.sql.ActiveQuery.one () will add `LIMIT 1` to the SQL expression. If your query can really return a lot of data, then you need to call `limit (1)` to set the limit, for example, ʻapp.models.Customer.find (). Limit (1) .one () `.

You can also use regular SQL queries to retrieve data and fill it in Active Record. To do this, use the Jii.sql.ActiveRecord.findBySql () method:

 //     var sql = 'SELECT * FROM customer WHERE status=:status'; app.models.Customer .findBySql(sql, {':status': app.models.Customer.STATUS_INACTIVE}) .all() .then(function(customers) { // ... }); 

Do not call the query creation methods after calling Jii.sql.ActiveRecord.findBySql () , they will be ignored.

Data access

As mentioned above, Active Record instances are populated with data from the SQL query results, and each row of the query result corresponds to one Active Record instance. You can access column values ​​through Active Record attributes, for example,

 //   "id"  "email"   "customer" app.models.Customer .findOne(123) .then(function(customer) { var id = customer.get('id'); var email = customer.get('email'); }); 

Getting data in objects

Retrieving data as an Active Record is convenient, but sometimes it may be sub-optimal due to the large memory consumption that is spent on creating Active Record instances. In this case, you can get them as ordinary objects. To do this, call the Jii.sql.ActiveQuery.asArray () method.

In fact, in JavaScript you get an array filled with objects. Therefore, it would be more correct to call the method asObject () , and there is such a method (synonym). But to save the API Yii 2, the asArray () method was left.

 //   ,    //    app.models.Customer.find() .asArray() // alias is asObject() .all() .then(function(customers) { // ... }); 

Saving data

Using Active Record, you can save data to the database by performing the following steps:

1. Receive or create an instance of Active Record;
2. Set new value to attributes
3. Call the Jii.sql.ActiveRecord.save () method to save the data.

For example,

 //      var customer = new app.models.Customer(); customer.set('name', 'James'); customer.set('email', 'james@example.com'); customer.save().then(function(success) { return app.models.Customer.findOne(123); }).then(function(customer) { //   customer.set('email', 'james@newexample.com'); return customer.save(); }).then(function(success) { // ... }); 

The Jii.sql.ActiveRecord.save () method can either add or update data lines, depending on the state of Active Record. If an instance was created using the `new` operator, the method will add a new string. If an instance was obtained via the find () method and similar to it, or the save () method was previously called, then the save () method
will update the data.

Data validation

The Jii.sql.ActiveRecord class is inherited from Jii.base.Model , so data validation is available in it. You can set the rules for the implementation by overriding the Jii.sql.ActiveRecord.rules () method and check for correct values ​​using the Jii.sql.ActiveRecord.validate () method.

When you call the Jii.sql.ActiveRecord.save () method, by default, the Jii.sql.ActiveRecord.validate () method will be automatically called. Only validated data should be stored in the database; If the data is not correct, the method will return `false` and you may get an error through the Jii.sql.ActiveRecord.getErrors () method or the like.

Change attribute set

Like regular models, an Active Record instance also supports changing attributes through object transfer. Using this method, you can assign the values ​​of several Active Record attributes by calling a single method. Remember that only safe attributes can be massively assigned.

 var values = { name: 'James', email: 'james@example.com' }; var customer = new app.models.Customer(); customer.setAttributes(values); customer.save(); 

Changed Attributes

When you call the Jii.sql.ActiveRecord.save () method, only the changed Active Record attributes are saved. An attribute is considered changed if its value has been changed. Note that data validation will be performed regardless of the existence of modified attributes.

Active Record automatically saves the list of changed attributes. It saves old versions of attributes and compares them with the latest version. You can get changed attributes through the Jii.sql.ActiveRecord.getDirtyAttributes () method.

To get old attribute values, call Jii.sql.ActiveRecord.getOldAttributes () or Jii.sql.ActiveRecord.getOldAttribute () .

Default values

Some of your columns in the table may have default values ​​defined in the database. You can pre-populate Active Record with these values ​​by calling the Jii.sql.ActiveRecord.loadDefaultValues ​​() method. This method is synchronous, because The database schema is preloaded when the connection is opened.

 var customer = new app.models.Customer(); customer.loadDefaultValues(); // customer.get('xyz')   `xyz`    -   `xyz`. 

Update multiple lines

The methods described above work with Active Record instances. To update several lines at the same time, you can call the static method Jii.sql.ActiveRecord.updateAll () :

 // UPDATE `customer` SET `status` = 1 WHERE `email` LIKE `%@example.com%` app.models.Customer.updateAll({status: app.models.Customer.STATUS_ACTIVE}, {'like', 'email', '@example.com'}); 

Data deletion

To remove a row from a table, it is necessary to call the Jii.sql.ActiveRecord.delete () method from the Active Record corresponding to this row.

 app.models.Customer .findOne(123) .then(function(customer) { customer.delete(); }); 

You can call the static Jii.sql.ActiveRecord.deleteAll () method to remove multiple rows by condition.
For example,

 app.models.Customer.deleteAll({status: app.models.Customer.STATUS_INACTIVE}); 

Work with related data

In addition to working with individual database tables, Active Record is able to link data through raw data. For example, customer data may be associated with orders. When declaring a corresponding connection in Active Record, you can get information about the customer’s order using the expression `customer.load ('orders')`, and you will receive an array of instances of `app.models.Order` at the output.

Addiction

To work with relational data using Active Record, you first need to declare the relationship in the Active Record class. For example,

 /** * @class app.models.Customer * @extends Jii.sql.ActiveRecord */ Jii.defineClass('app.models.Customer', /** @lends app.models.Customer.prototype */{ // ... getOrders: function() { return this.hasMany(app.models.Order.className(), {customer_id: 'id'}); } }); /** * @class app.models.Order * @extends Jii.sql.ActiveRecord */ Jii.defineClass('app.models.Order', /** @lends app.models.Order.prototype */{ // ... getCustomer: function() { return this.hasOne(app.models.Customer.className(), {id: 'customer_id'}); } }); 

In the above code, we declared the ʻorders` ratio for the `app.models.Customer` class, and the` customer` relation for the `app.models.Order` class.

Each relational method should be named `getXyz` (get + the name of the relationship with the first letter in lower case). Please note that relationship names are * case sensitive *.

In regards, you must provide the following information:

• The multiplicity of the connection: specified when calling the Jii.sql.ActiveRecord.hasMany () or Jii.sql.ActiveRecord.hasOne () methods. In the example above, the customer has many orders, and the order has only one customer.
• The name of the associated class Active Record: is specified as the first parameter of the above methods. It is recommended to get the class name through `Xyz.className ()`, firstly, to check the existence of the class at the stage of constructing the relationship, and secondly, for the IDE to tell you the name of the class when writing.
• Relationship between two table schemas: defines the column (s) through which the two types of data are connected. The object values ​​are the primary data columns, and the keys are the related data columns.

Access to related data

After declaring a relationship, you can access related data through the name of the relationship. If you are sure that the associated data is already loaded into Active Record, then you can get the associated Active Record in the same way as accessing the properties of an object through the get () method. Otherwise, it is better to use the Jii.sql.ActiveRecord.load () method to load the associated data, which will always return the `Promise` object, but will not send an extra query to the database if the link has already been loaded before.

 // SELECT * FROM `customer` WHERE `id` = 123 app.models.Customer .findOne(123) .then(function(customer) { // SELECT * FROM `order` WHERE `customer_id` = 123 return customer.load('orders'); }) .then(function(orders) { // orders -    `app.models.Order` }); 

If the relationship is declared by the Jii.sql.ActiveRecord.hasMany () method, then the relationship will be represented by an array of Active Record instances (or an empty array). If using Jii.sql.ActiveRecord.hasOne () , then the relationship data will be represented by an instance of Active Record or `null` if there is no data.

When accessing a relation for the first time, a SQL query will be executed in the database, as shown in the example above. When re-accessing, the request will not be executed.

Relationships through an additional table (Junction Table)

When modeling databases, when the connection is between two Many-Many tables, an additional table is usually added - the Junction Table . For example, the `order` table and the` item` table can be linked using the `order_item` table.

When declaring such relationships, you need to call the Jii.sql.ActiveQuery.via () or Jii.sql.ActiveQuery.viaTable () methods with the indication of the additional table. The difference between these methods is that the first one indicates the transition table from the point of view of the current name of the relation, while the last one directly indicates the additional table. For example,

 /** * @class app.models.Order * @extends Jii.sql.ActiveRecord */ Jii.defineClass('app.models.Order', /** @lends app.models.Order.prototype */{ // ... getItems: function() { return this.hasMany(app.models.Item.className(), {id: 'item_id'}) .viaTable('order_item', {order_id: 'id'}); } }); 

or alternatively

 /** * @class app.models.Order * @extends Jii.sql.ActiveRecord */ Jii.defineClass('app.models.Order', /** @lends app.models.Order.prototype */{ // ... getOrderItems: function() { return this.hasMany(app.models.OrderItem.className(), {order_id: 'id'}); }, getItems: function() { return this.hasMany(app.models.Item.className(), {id: 'item_id'}) .via('orderItems'); } }); 

Using relationships declared with an additional table is similar to regular relationships. For example,

 // SELECT * FROM `order` WHERE `id` = 100 app.models.Order .findOne(100) .then(function(order) { // SELECT * FROM `order_item` WHERE `order_id` = 100 // SELECT * FROM `item` WHERE `item_id` IN (...) return order.load('items'); }) .then(function(items) { // items -   `app.models.Item` }); 

Lazy Loading and greedy loading

In the section for accessing related data, we told you that you can access a relation from Active Record via get () or load () methods. The SQL query will be sent to the database only the first time it accesses the related data. Such data loading methods are called lazy loading.
For example,

 // SELECT * FROM `customer` WHERE `id` = 123 app.models.Customer .findOne(123) .then(function(customer) { // SELECT * FROM `order` WHERE `customer_id` = 123 customer.load('orders').then(function(orders) { // SQL    return customer.load('orders'); }).then(function(orders2) { //   ,      <i>get()</i>. var orders3 = customer.get('orders'); }); }); 

Lazy load is very convenient to use. However, this can cause performance problems when you need to access related tied to multiple instances of Active Record. Consider the following code sample, how many SQL queries will be executed?

 // SELECT * FROM `customer` LIMIT 100 app.models.Customer.find() .limit(100) .all() .then(function(customers) { return Promise.all(customers.map(function(customer) { // SELECT * FROM `order` WHERE `customer_id` = ... return customer.load('orders'); })); }).then(function(result) { var firstOrder = result[0][0]; // ... }); 

In this example, 101 SQL queries will be executed! Because for each client applications will be received through a separate request. To solve this performance problem, you can use the eager loading * approach *, as shown below,

 // SELECT * FROM `customer` LIMIT 100; // SELECT * FROM `orders` WHERE `customer_id` IN (...) app.models.Customer.find() .with('orders') .limit(100) .all() .then(function(customers) { customers.forEach(function(customer) { //  SQL  var orders = customer.get('orders'); }); }); 

You can load one or more relationships along with the main entry. You can even load nested relationships immediately. For example, if ʻapp.models.Customer` is associated with ʻapp.models.Order` through the `orders` relationship, and` app.models.Order` is associated with `Item` through` items`. When you query ʻapp.models.Customer`, you can immediately load the `items` relationship by specifying` orders.items` in the with () method.

The following code shows the different usage of Jii.sql.ActiveQuery.with () . We assume that the class `app.models.Customer` has two relationships:` orders` and `country`, while the class` app.models.Order` has one relationship - `items`.

 //    "orders"  "country" app.models.Customer.find() .with('orders', 'country') .all() .then(function(customers) { // ... }); //      app.models.Customer.find() .with(['orders', 'country']) .all() .then(function(customers) { //  SQL  var orders = customers[0].get('orders'); var country = customers[0].get('country'); }); //    "orders"    "orders.items" app.models.Customer.find() .with('orders.items') .all() .then(function(customers) { //         //  SQL  var items = customers[0].get('orders')[0].get('items'); }); 

You can load forced deeply nested relationships, such as `abcd`. All parent relationships will be forcibly loaded.

With greedy relations loading, you can configure the corresponding request by passing an anonymous function. For example,

 //          // SELECT * FROM `customer` // SELECT * FROM `country` WHERE `id` IN (...) // SELECT * FROM `order` WHERE `customer_id` IN (...) AND `status` = 1 app.models.Customer.find() .with({ country: 'country', orders: function (query) { query.andWhere({'status': app.models.Order.STATUS_ACTIVE}); } }) .all() .then(function(customers) { // ... }); 

When setting up a relational query for a link, you must specify the name of the relationship as the key of the object and use the anonymous function as the value of the corresponding object. The first argument of the nonym function is the `query` parameter, which is a Jii.sql.ActiveQuery object. In the example above, we modify the request by adding an additional condition on the status of the order.

Inverse relationship

Relationships between Active Record classes are often inversely related. For example, the class `app.models.Customer` is associated with` app.models.Order` through the relationship `orders`, and the class` app.models.Order` is inversely associated with the class ʻapp.models.Customer` through the relationship `customer` .

 /** * @class app.models.Customer * @extends Jii.sql.ActiveRecord */ Jii.defineClass('app.models.Customer', /** @lends app.models.Customer.prototype */{ // ... getOrders: function() { return this.hasMany(app.models.Order.className(), {customer_id: 'id'}); } }); /** * @class app.models.Order * @extends Jii.sql.ActiveRecord */ Jii.defineClass('app.models.Order', /** @lends app.models.Order.prototype */{ // ... getCustomer: function() { return this.hasOne(app.models.Customer.className(), {id: 'customer_id'}); } }); 

Now consider the following code snippet:

 // SELECT * FROM `customer` WHERE `id` = 123 app.models.Customer .findOne(123) .then(function(customer) { // SELECT * FROM `order` WHERE `customer_id` = 123 return customer.load('orders'); }).then(function(orders) { var order = orders[0]; // SELECT * FROM `customer` WHERE `id` = 123 return order.load('customer'); }).then(function(customer2) { //  "not the same" console.log(customer2 === customer ? 'same' : 'not the same'); }); 

We assume that the objects `customer` and` customer2` are the same, but actually this is not the case. . `order.customer` SQL `customer2`.

SQL , , `customer` * * `orders` Jii.sql.ActiveQuery.inverseOf() .

 /** * @class app.models.Customer * @extends Jii.sql.ActiveRecord */ Jii.defineClass('app.models.Customer', /** @lends app.models.Customer.prototype */{ // ... getOrders: function() { return this.hasMany(app.models.Order.className(), {customer_id: 'id'}).inverseOf('customer'); } }); 

:

 // SELECT * FROM `customer` WHERE `id` = 123 app.models.Customer .findOne(123) .then(function(customer) { // SELECT * FROM `order` WHERE `customer_id` = 123 return customer.load('orders'); }).then(function(orders) { var order = orders[0]; // SELECT * FROM `customer` WHERE `id` = 123 return order.load('customer'); }).then(function(customer2) { //  "same" console.log(customer2 === customer ? 'same' : 'not the same'); }); 

: Many-Many, (Junction Table).

, . , . Active Record :

 app.models.Customer .findOne(123) .then(function(customer) { var order = new app.models.Order(); order.subtotal = 100; // ... //  ,   "customer"  `app.models.Order`  . order.customer_id = customer.id; order.save(); }); 

Active Record Jii.sql.ActiveRecord.link() , :

 app.models.Customer .findOne(123) .then(function(customer) { var order = new app.models.Order(); order.subtotal = 100; // ... order.link('customer', customer); }); 

Jii.sql.ActiveRecord.link() Active Record, . Active Record . , `customer_id` `app.models.Order`.

: Active Record.

Jii.sql.ActiveRecord.link() , . , , `app.models.Order` `app.models.Item`:

 order.link('items', item); 

`order_item` .

Active Record, Jii.sql.ActiveRecord.unlink()|unlink() .
For example,

 app.models.Customer.find() .with('orders') .all() .then(function(customer) { customer.unlink('orders', customer.get('orders')[0]); }); 

, Jii.sql.ActiveRecord.unlink() , , `null`. , `isDelete` `true`, .

Active Record Jii , .
, Jii — , , - Jii. affka@affka.ru.

— jiiframework.ru
GitHub — github.com/jiisoft