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
to
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?
