Skip to content

Agile Data for Yii2 developers

Jump to bottom
Romans Malinovskis edited this page Jul 10, 2016 · 7 revisions

This guide is designed to introduce Agile Data to developers familiar with Yii2.

The purpose for this article is not to discredit Yii2, but rather highlight the differences in design, help you better understand the differences and decide for yourself which of the two is more suitable.

THIS ARTICLE IS WORK IN PROGRESS. YOU ARE WELCOME TO MODIFY IT OR ADD BELOW.

Yii2 References:

Scope of functionality

Agile Data only focuses on data interaction and does not offer any functionality beyond that. It tries to be minimalistic and compact. No MVC or Config file handling. You can use Agile Data inside your Yii project easily, but not other way around.

Agile Data models

A model implementation in Agile Data is similar to Active Record in Yii although there are quite a few important differences. I'll start with similarities:

// Yii2
namespace app\models;
use yii\db\ActiveRecord;
class Country extends ActiveRecord { }

$countries = Country::find()->orderBy('name')->all();

Agile Data is not very specific about namespaces, so you can use any. Also generally you would prefix your class with "Model_":

// Agile Data
namespace mydb;
class Model_Country extends \atk4\data\Model { }

$m = new Model_Country($db, 'country');
$m->addFields(['name','code']);
$countries = $m->setOrder('name')->export();

Field Names

With Agile Data you should always remember that Model field "name" may not be country.name after all. Agile Data allow you to map business model fields into different ways to fetch and store the field:

$m->addField('name', ['actual'=>'county_name']);
$countries = $m->setOrder('name')->export();
// [ ['id'=>1, 'name'=>'UK' ], .. ]

Even export() respects the mappings. Here is another example::

$m->addFields(['code','short_name']);
$m->addExpression('name', 'concat([code], " - ",  [short_name])');
$countries = $m->setOrder('name')->export(['name']);
// [ ['name'=>'UK' ], .. ]

// Query: select `id`, contact(`code`, " - ", `short_name`) from country

Active Query

Agile Data Models live in a "Business Universe" as explained above. You can dip into the "Persistence" through "actions" (but you are discouraged from that!).

$m = new Model_User($db);
echo $count = $m->action('count')->where('foo=bar')->getRow();  // how many users are there?

The action is a more generic concept, though and actions will work even if $db is not actually a SQL. The capabilities of the actions may differ. However as you most likely to use SQL anyway, you might want to learn about SQL Actions.

[http://dsql.readthedocs.io/en/develop/queries.html](DSQL Query Class) is used for SQL actions, which is very similar to db\Query. When calling action(), the query will "map" your business logic into "query":

$m = new Model_Client($db);
$m->addCondition('is_vip', true);
$m->join('country', ['prefix'=>'country'])->addFields(['name','code']);
$m->hasMany('Order')->addField('total_orders',['aggregate'=>'sum','field'=>'amount']); 
$m->addCondition('total_orders', '>', 1000);

echo $count = $m->action('select')->getDebugQuery();  
// gives you a SQL query with join and a few sub-selects

When you work with Agile Data, you are recommending to express yourself through business logic and let Agile Data map it into queries for you. This way you are less likely to make mistake and you are not loosing on the performance anyway.

Expressions and DSQL

Yii allows you to add scope on :

$UserStockQuery = Stock::find()
->where(['stockActive' => 1])
->andWhere(['tenantId' => $this->tenant]);

The idential code would be:

$UserStockQuery = (new Model_Stock($db))->action('select')
->where('stockActive', 1)
->where('tenantId', $this->tenant);

although a better practice in Agile Data would be:

$UserStock = (new Model_Tenant($db))->load($this->tenant)->ref('Stock');

Complex Queries

http://www.yiiframework.com/wiki/826/yii2-query-build-for-complexity-sql/

DSQL is much more practical when it comes to complex expressions. The similar logic can be built easily in Agile Data and DSQL:

Expression templates

// Yii2
$e = new Expression("if(t.Status_Id in ($excludeStatus),'No',
    if(t.Version <> 1,
    if(DATE_ADD(CURDATE(),INTERVAL m.Approved DAY)>p.New_Version_Due_Date and IsNULL(Forecast_Qty),'Yes','No'),
    if(DATE_ADD(CURDATE(),INTERVAL m.Approved DAY)>p.Mass_Lock_Due_Date and IsNULL(Forecast_Qty),'Yes','No')
    ))");

// Agile Data
$e = new \atk4\DSQL\Expression("if(t.Status_Id in ([excludeStatus]),'No',
    if(t.Version <> [version],
    if(DATE_ADD(CURDATE(),INTERVAL m.Approved DAY)>p.New_Version_Due_Date and IsNULL(Forecast_Qty),'Yes','No'),
    if(DATE_ADD(CURDATE(),INTERVAL m.Approved DAY)>p.Mass_Lock_Due_Date and IsNULL(Forecast_Qty),'Yes','No')
    ))", [$exclude_status_expression, $version_int]);

The expression can then be used inside query:

// Agile Data
$RegionalPart->action('select')->field($e, 'FO_Due');

Points to note:

  • Expressions can be nested
  • Expressions can contain parameters that will properly map into PDO parameters
  • Expressions can be used in any part of the query.

More importantly, you can use expression inside your models too:

$RegionalPart->addExpression('FO_Due', $e);

Which makes this expression a regular field.

Joins

You can define Joins inside a model:

$RegionalPart->join('eop_status', 'Status_Id') ->addField('Status', 'Status_Description');

$j_p = $RegionalPart->join('eop_part', 'Part_Id'); $j_p->join('eop_platform', 'Platform_Id') ->addFields(['Platform_Status_Id', 'Platform_Sales_Kpcs']);

However if you already have a basic model implementing "Platform" you can do this:

$RegionalPart->join('eop_platform', 'Platform_Id') ->importModel('Platform');

which saves you a lot of typing and helps you avoid errors as it will also import necessary conditions.

Scopes

Yii framework used concept 'scope' to limit accessible records. Agile Data does not implement 'scope' as a separate concept, but addConditon() and ability to extend models offer a much more powerful alternative:

class Model_AffordableItem extends Model_Item {
    private $current_user_id; 
    function init() {
        parent::init();
        
        $this->addExpression('cost', '[discount]*[price]);

        $cu = new Model_User($this->persistence);
        $cu->withId($this->current_user_id);
        $this->addCondition(
            '[cost]<=[money]',
            ['money'=>$cu->action('field', ['balance'])]
        );
    }
}

// How to show only items that current user can afford with a single SQL query?

$item_list = new Model_AffordableItems($db, ['current_user_id', $logged_in_user_id]);

Iterating References

Suppose you need to set "free_shipping" to 'true' for all VIP clients. Traditionally you would do this:

// Yii
$ar = Model::Client()->with('is_vip', 1)->findAll();
foreach ($ar as $item) {
  $subItem = $item->settings; // related model containing settings
  $subItem->free_shipping = 1;
  $subItem->save(false); 
}

With traditional ORMs this generates bunch of queries:

  • one big query to fetch all VIP IDs (lazy loading)
  • when traversing into 'settings' the data is loaded for both 'item'
  • when accessing free_shipping, data is loaded for 'settings'
  • calling save data is saved.

So with 20 VIP clients you're looking at:

  • one query that retrieves 20 IDs
  • 20 read queries that fetch ALL client data
  • 20 read queries that fetch ALL settings data
  • 20 update queries (I hope they will be updating only single field, no tryin to update all)

in Agile Data you can do that in one query that implies exactly the same logic:

$ar = new Model_Client($db);
$ar->addCondition('is_vip', true);
$settings = $ar->ref('settings');

$settings->action('update')->set('free_shipping', 1)->execute();

If you are insisting on doing it on record-by-record basis, you can do this:

$ar = new Model_Client($db);
$ar->addCondition('is_vip', true);
$settings = $ar->ref('settings')->onlyFields(['free_shipping']);

foreach($settings as $subItem) {
    $subItem['free_shipping'] = 1;
    $subItem->save();
}

So with 20 VIP clients you're looking at:

  • one query that retrieves only 2 fields from 20 records
  • 20 update queries that only change free_shipping

Either way Agile Data reduces number of queries, size of retrieved data or amount of updated columns.

Agile Data, Documentation and Wiki content is licensed under MIT and (c) by Agile Toolkit Limited UK

Clone this wiki locally