How to do group by with ORM?

Icinga Web Modules
, , ,
1

I had quite the fight to get a n:m relation working in the module I write.

And now I would like the servers be grouped so that the application rows aren’t duplicated, when an application has more then one server.

So from

image
image1078×412 10.7 KB

to
image
image1080×271 7.96 KB

Is this possible via ORM in the SQL query or do I need to group the data later via PHP?

For reference here my DB structure and code.

I started with @moreamazingnick’s scaffoldbuilder module and referenced the module training YouTube video and documentation.

DB structure

modulename_application
id
name
team_id
modulename_team
id
name
modulename_server
id
name
modulename_application_server
application_id
server_id

Code

Model

Application.php

<?php
namespace Icinga\Module\Modulename\Model;

use ipl\Orm\Relations;

class Application extends DbModel
{
    public function getTableName(): string
    {
        return 'modulename_application';
    }

    public function getKeyName()
    {
        return 'id';
    }

    public function getColumnDefinitions(): array
    {
        return [
            'name'=>[
                'fieldtype'=>'text',
                'label'=>'Name',
                'description'=>t('Application Name'),
                'required'=>true
            ],
        ];
    }

    public function createRelations(Relations $relations)
    {
        // 1:1 relation to team
        $relations->belongsTo('team', Team::class)
            ->setCandidateKey('team_id');

        // n:m relation to servers
        $relations->belongsToMany('server', Server::class)
            ->through(ApplicationServer::class)
            ->setForeignKey('application_id')
            ->setTargetForeignKey('server_id'); // took me while to figure this out!
    }
}

Controller

ApplicationController.php

<?php
namespace Icinga\Module\Modulename\Controllers;

use Icinga\Exception\ConfigurationError;

use Icinga\Module\Modulename\ApplicationRestrictor;
use Icinga\Module\Modulename\Common\Database;
use Icinga\Module\Modulename\Controller;
use Icinga\Module\Modulename\Model\Application;

use Icinga\Module\Modulename\ApplicationTable;
use Icinga\Module\Modulename\Web\Control\SearchBar\ObjectSuggestions;


use ipl\Web\Control\LimitControl;
use ipl\Web\Control\SortControl;

use ipl\Web\Url;
use ipl\Web\Widget\ButtonLink;

class ApplicationsController extends Controller
{

    public function indexAction()
    {

        if ($this->hasPermission('modulename/application/modify')) {
            $this->addControl(
                (new ButtonLink(
                    $this->translate('New Application'),
                    Url::fromPath('modulename/application/new'),
                    'plus'
                ))->openInModal()
            );
        }

        $this->addTitleTab($this->translate('Applications'));

        try {
            $conn = Database::get();
        } catch (ConfigurationError $_) {
            $this->render('missing-resource', null, true);
            return;
        }

        $models = Application::on($conn)
            ->with(['team','server']) // this quite the struggle to figure out!
            ->withColumns([]);

        #print_r($models->dump());
        #exit();
        $sortColumns = [
            'name' => $this->translate('Name'),
            'modulename_application_server_name' => 'Server',
        ];
        $restrictor = new ApplicationRestrictor();
        $restrictor->applyRestrictions($models);

        $limitControl = $this->createLimitControl();
        $paginator = $this->createPaginationControl($models);
        $sortControl = $this->createSortControl($models, $sortColumns);

        $searchBar = $this->createSearchBar($models, [
            $limitControl->getLimitParam(),
            $sortControl->getSortParam()
        ]);

        if ($searchBar->hasBeenSent() && ! $searchBar->isValid()) {
            if ($searchBar->hasBeenSubmitted()) {
                $filter = $this->getFilter();
            } else {
                $this->addControl($searchBar);
                $this->sendMultipartUpdate();

                return;
            }
        } else {
            $filter = $searchBar->getFilter();
        }

        $models->peekAhead($this->view->compact);

        $models->filter($filter);

        $this->addControl($paginator);
        $this->addControl($sortControl);
        $this->addControl($limitControl);
        $this->addControl($searchBar);

        $this->addContent((new ApplicationTable())->setData($models));

        if (! $searchBar->hasBeenSubmitted() && $searchBar->hasBeenSent()) {
            $this->sendMultipartUpdate(); // Updates the browser search bar
        }
    }

    public function completeAction()
    {
        $this->getDocument()->add(
            (new ObjectSuggestions())
                ->setModel(Application::class)
                ->forRequest($this->getServerRequest())
        );
    }

    public function searchEditorAction()
    {
        $editor = $this->createSearchEditor(Application::on(Database::get()), [
            LimitControl::DEFAULT_LIMIT_PARAM,
            SortControl::DEFAULT_SORT_PARAM
        ]);

        $this->getDocument()->add($editor);
        $this->setTitle(t('Adjust Filter'));
    }
}

@moreamazingnick Application::on($conn)->with(['team','server']) was hard to figure out maybe a note in the scaffoldbuilder or @elippmann a doc folder in GitHub - Icinga/ipl-orm would have helped.

View

ApplicationTable.php

<?php
namespace Icinga\Module\Modulename;

use Icinga\Authentication\Auth;
use Icinga\Module\Modulename\Model\Application;
use Icinga\Web\Url;
use ipl\Orm\Model;

/**
 * Table widget to display a list of Applications
 */
class ApplicationTable extends DataTable
{
    protected $defaultAttributes = [
        'class'            => 'usage-table common-table table-row-selectable',
        'data-base-target' => '_next'
    ];

    public function createColumns()
    {
        $columns = [];
        foreach ((new Application())->getColumnDefinitions() as $column=>$options){

            if(is_array($options)) {
                $fieldtype = $options['fieldtype'] ?? "text";

                unset($options['fieldtype']);

                if ($fieldtype === "autocomplete" || $fieldtype === "text" || $fieldtype === "select") {
                    $columns[$column] = $options['label']??$column;

                }elseif ($fieldtype === "checkbox"){
                    $columns[$column."_text"] = [
                        'label'  => $options['label']??$column,
                        'column' => function ($data) use ($column) {
                            return $data->{$column}?t("Yes"):t("No");
                        }
                    ];
                }elseif ($fieldtype === "localDateTime" ){
                    $columns[$column."_txt"] = [
                        'label'  => $options['label']??$column,
                        'column' => function ($data) use ($column) {
                            if($data->{$column}!=null){
                                return $data->{$column}->format("c");
                            }
                            return t("not set");
                        }
                    ];
                }
            }else{
                $columns[$column] = $options;
            }
        }
        $columns['team.name'] = [
            'label' => t('Team'),
            'column' => function ($data) {
                return $data->team?->name ?? t('not set');
            }
        ];
        $columns['Server'] = [
            'label' => t('Server'),
            'column' => function ($data) {
                return $data->server?->name ?? t('not set');
            }
        ];

        return $columns;
    }


    protected function renderRow(Model $row)
    {
        $tr = parent::renderRow($row);

        if (Auth::getInstance()->hasPermission('modulename/application/modify')) {
            $url = Url::fromPath('modulename/application/edit', ['id' => $row->id]);

            $tr->getFirst("td")->getAttributes()->add(['href' => $url->getAbsoluteUrl(), 'data-icinga-modal' => true,
                'data-no-icinga-ajax' => true]);

        }

        return $tr;
    }
}

@moreamazingnick do I really need to add the columns added by the relations here or is there a better way?

2

Experimented with a sub query but never got it working.

I currently use a naked SQL query in the Applications controller and get the result but this way I lost all searching, sorting and paging :frowning:

3

I “solved” it by adding a query, to get the servers, for every row in the ApplicationTabe.

ApplicationTable.php:

...
use ipl\Sql;
use ipl\Sql\Select;

/**
 * Table widget to display a list of Applications
 */
class ApplicationTable extends DataTable
{
    private $conn;

    /**
     * Set connection to resolve servers
     *
     * @param   Sql\Connection  $conn
     *
     * @return  $this
     */
    public function setConn($conn)
    {
        if (! $conn instanceof Sql\Connection) {
            throw new \InvalidArgumentException('Data must be an array or an instance of Traversable');
        }

        $this->conn = $conn;

        return $this;
    }
...

    public function createColumns()
    {
...
        $columns['Server'] = [
            'label' => t('Server'),
            'column' => function ($data) {
                $q = (new Select())
                    ->from('modulename_server ms')
                    ->columns(['ms.name'])
                    ->join('modulename_application_server mas', 'ms.id = mas.server_id')
                    ->where('mas.application_id = ?', $data->id);

                $servers = $this->conn->select($q)->fetchAll();

                return implode(', ', array_column($servers, 'name'));
            }
        ];

        return $columns;
    }

ApplicationsController.php:
removed server from ->with(['team'])

...
        $this->addContent((new ApplicationTable())
                          ->setData($models)
                          ->setConn($conn)); // added setConn to hand the DB connection to the table view
...

Next I would try to overload the Query class in my module and add the missing group by feature.

4

now I understand what you actually want.

You don’t need to “group by” you want to join and access other properties and concatenate the values.

That’s actually the problem with not sharing the full code like putting it on github.

In the Model you most likely missing: createRelations

if you then have the ->with([‘xxxx’]) in the Controller that calls the ApplicationTable, you can access the relation and iterate over it.

but you could also create a function in your model that returns exactly that text and call that function wherever you need that

6

If you look in my first post at Application.php , I have the relations defined.

    public function createRelations(Relations $relations)
    {
        // 1:1 relation to team
        $relations->belongsTo('team', Team::class)
            ->setCandidateKey('team_id');

        // n:m relation to servers
        $relations->belongsToMany('server', Server::class)
            ->through(ApplicationServer::class)
            ->setForeignKey('application_id')
            ->setTargetForeignKey('server_id'); // took me while to figure this out!
    }
}

and if I access the data with with(['server']) I get duplicated lines, one application row for ever server, that’s associated with it.

Are you just talking about moving the server lookup, from the view, into the controller or model?

I guess, it would be cleaner but still not integrated with search and sort, until I can get the “GROUP BY” it into the main query.

7

@moreamazingnick, I figured it out:

ApplicationController.php

...
        $models = Application::on($conn)
            ->with(['team', 'server'])
            ->columns([
                'id',
                'name',
                'team_id',
                'team.name',
                'server.name'
            ]);

        $models
            ->getSelectBase()
            ->groupBy('modulename_application.id');

        $models->withColumns([
            'server_list' => new \ipl\Sql\Expression('GROUP_CONCAT(modulename_application_server.name ORDER BY modulename_application_server.name SEPARATOR ", ")')
        ]);
...

groupBy() is only available after getSelectBase()!

Maybe, there’s a better way and I still get search filters, that result in invalid queries but possibly I added to many relations by now.

image
image402×217 6.57 KB