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.