atk4

Is it possible tu use a UNION sql in agiletoolkit?

I want to combine to tables: the sql is:

select name, extract(DAY from fechaNacimiento) from alumno
union
select name,extract(DAY from fechaNacimiento) from asesor

Is it possible to use that query in a dsql() or dq() statement?

Solution

  • I am author of Agile Toolkit and UnionModel implementation is now available as a separate module for Agile Data.

    http://www.agiletoolkit.org/data/extensions/report

    With the extension, assuming you have models for "Alumno" and "Asesor" for which fields name/day are defined to produce above queries, you can construct a Union Model based on domain logic:

    $union = new \atk4\report\UnionModel($db);
$union->addNestedModel(new Model_Alumno());
$union->addNestedModel(new Model_Asesor());
$union->addField('name');
$union->addField('day');

    Resulting model will be read-only but can be fully used anywhere, will support grouping, conditions, limits and will query only fields necessary.

    Solution without using "Reporting Extension" would involve aligning queries from nested models manually and putting them inside Expression:

    $expr = new \atk4\dsql\Expression(
    "select ... from ([] union [])",
    [
        $m1->action('select', [$fields]), 
        $m2->action('select', [$fields]),
    ]
);

    Support for versions prior to 4.4 are now considered obsolete.