Agile Data ┬╗ Agile Reporting Extension

Writing efficient report queries for ORM has always been complex. Agile Data defines models differently allowing for extensions like this to exist.

Union Model

Use your Domain Model logic to build efficient "UNION" queries for all your reporting needs (SQL only). Example:

$union = new \atk4\report\UnionModel($db);

$union->addNestedModel(new Invoice());
$union->addNestedModel(new Payment(), ['amount'=>'-[amount]']);

$union->addFields(['amount', 'project_id', 'date']);

$union->join('project', 'project_id')
  ->addField('project_name', 'name');

$union->groupBy(['date','project'], ['amount'=>'sum']);

$report_data = $union->export(['date', 'project_name', 'sum']);

The above example will combine your Invoice and Payment records together for the reporting purposes. Compared to Stored Procedures, the above approach is efficient and requires no maintenance.

There are many uses for Union Model. Next example is to implement multi-entity search for your site:

class Search extends \atk4\report\UnionModel {
    function init() {
        parent::init();

        $this->addNestedModel(new Article())
          ->addExpression('type', '"article"');
        $this->addNestedModel(new Blog())
          ->addExpression('type', '"blog"');
        $this->addNestedModel(new ShopItem())
          ->addExpression('type', '"shop-item"');

        $this->addField('published', ['type'=>'date']);
        $this->addField('title');
        $this->addField('author_id');
        $this->addField('is_public', ['type'=>'boolean']);
        $this->join('author', 'author_id')->addField('author_name', 'name');

        $this->addCondition('is_public', true);         

        $this->addField('keywords');
        $this->addField('search_blob');
    }

    /**
     * Deep full-text search condition
     */
    function search($q) {
        foreach($this->union as $m) {
            $m->addCondition($m->expr(
              "match (title, search_blob) against ([] in boolean mode)", 
              [$q]
            ));
        }
        return $this;
    }
}

// Now this is how we can get list of all matched articles now:

$s = new Search($db);
$s->addCondition('published', '>', new DateTime("2015-01-01"));
$s->search($_GET[$q]);
$s->setLimit(20);

$data = $s->export();
 // 20 matched rows containing type, author_name, title
Agile Audit Zend Combine
UNION using Domain Models UNION ALL table-level only
Map missmatched columns, expressions yes custom
Use of PDO parameters yes no
Join results from UNION with one or more tables yes as raw code
Allow nested models to contain joins and sub-queries yes as raw code
Conditions will be applied to all nested models yes no
Grouping will be performed on nested models yes no
Secondary grouping to elliminate duplicated yes no
Respect implied ACL conditions (e.g. soft-delete) yes no
Update, Add or Delete records into UnionModel no no
Full drop-in repalacement for your basic Model yes no
Support for selective fields yes no
Support for aggregation (group by) yes no
Use in Expressions and column aggregation yes no

Notice: please report to us if you notice any inaccuracy. The table above depicts anly basic functionality.

When to use?

Here is a list of growing use-cases that we have collected, where UNION models save tons of time:

  • Create General Ledger report from over 10+ different accounting entities
  • Calculate client statement based on Invoices and Payments
  • Aggregate client statement model into single "balance" column
  • Perform multi-table full-text search and order results by relevancy
  • (tell us about your use case!!)

Download and Install

Audit Extension is currently in Beta. You need to contact us if you wish to get early access.