Creating a nice UI for managing Many-to-Many relations


#1

Hey there,

this is another task I am set to realize in my current project. I want to track my progress here so others can re-use it easily.

If some UI for doing this is already present somewhere and I am just too stupid to find, PLEASE tell me :slight_smile:

Ok, here is what I want to do:
I have guests and groups, they have an Many-to-Many relation ship: A guest can be in several groups and a group can have several persons.
The Database model is pretty standard, there is a mapping table for storing the relations:

So first I need to create the data model correctly and most of all build a nicely usable UI.

Data Model

I found some ideas in this forum post: Many-to-Many relation visualisation
A more recent one is found in the Agile Data documentation: http://agile-data.readthedocs.io/en/develop/advanced.html?highlight=hasMany#creating-many-to-many-relationship
This made me create:

class Group extends \atk4\data\Model {
   
    public $table = 'group';

    function init()
    {
        parent::init();

        $this->addFields([
            'name',
            'name_contact',
            ['amount_guests', 'type'=>'integer'],
            ['amount_interested', 'type'=>'integer'],
            ['standard_email', 'type'=>'email'],
            'standard_phone',
            'coming_from',
        ]);
		$this->hasOne('group_origin_id', new GroupOrigin());
		$this->hasMany('GuestToGroup', new GuestToGroup());
    	$this->hasMany('Guest', [function($m) {
			$p = new Guest($m->persistence);
			$j = $p->join('guest_to_group.guest_id');
			$j->hasOne('group_id', 'Group');
		}, 'their_field'=>'group_id']);
		
		$this->addHook('beforeDelete',function($m){
    		$m->ref('GuestToGroup')->action('delete')->execute();
    	});
    }
}



class Guest extends \atk4\data\Model {
   
    public $table = 'guest';

    function init()
    {
        parent::init();

        $this->addFields([
            'firstname',
            'lastname',
            'gender',
            ['birthday', 'type'=>'date'],
            'nickname',
            ['weight', 'type'=>'integer'],
			['height', 'type'=>'integer'],
            'shoesize',
        ]);
		$this->hasMany('GuestToGroup', new GuestToGroup());
		$this->hasMany('Group', [function($m) {
			$p = new Group($m->persistence);
			$j = $p->join('guest_to_group.group_id');
			$j->hasOne('guest_id', 'Guest');
		}, 'their_field'=>'guest_id']);
		
		$this->addHook('beforeDelete',function($m){
    		$m->ref('GuestToGroup')->action('delete')->execute();
    	});
    }
}

class GuestToGroup extends \atk4\data\Model {
    
    public $table='guest_to_group';
    
    function init()
    {
        parent::init();
        $this->hasOne('guest_id', new Guest());
        $this->hasOne('group_id', new Group());
    }
}


class Guest extends \atk4\data\Model {
   
    public $table = 'guest';

    function init()
    {
        parent::init();

        $this->addFields([
            'firstname',
            'lastname',
            'gender',
            ['birthday', 'type'=>'date'],
            'nickname',
            ['weight', 'type'=>'integer'],
			['height', 'type'=>'integer'],
            'shoesize',
        ]);
		$this->hasMany('GuestToGroup', new GuestToGroup());
    }
}

class GuestToGroup extends \atk4\data\Model {
    
    public $table='guest_to_group';
    
    function init()
    {
        parent::init();
        $this->hasOne('guest_id', new Guest());
        $this->hasOne('group_id', new Group());
    }
}

Lets hope this code does the job :slight_smile:

UI - Ideas

How could a good UI for my case look like? In my case its normally removing a single guest to a group or adding a single guest to a group.

Removing is easy:
Display all linked groups/guests, and have a delete button behind for everyone, something like this:
mtom2

But adding seems more difficult. As there will be quite some guests and groups (around 5000/700), The only sensible way to do this is with a search. I found a searchable dropdown in Sematic UI which looks nice at first glance:
https://semantic-ui.com/modules/dropdown.html#search-in-menu

So thats where I want to get, lets get started :slight_smile:

Best regards
Philipp


#2

Probably a good implementation would be to have a AutoComplete with “multiple” sections (see here https://semantic-ui.com/modules/dropdown.html)

Autocomplete implementation was recently merged, but it’s single value only.

Look at the implementation of it: https://github.com/atk4/ui/blob/develop/src/FormField/AutoComplete.php or even Dropdown: https://github.com/atk4/ui/blob/develop/src/FormField/DropDown.php

You may be able to find way to enable multiple selection and, most importantly, capture and store that inside connecting table.

Another option is to use “Grid” with checkboxes. https://github.com/atk4/ui/blob/develop/src/TableColumn/CheckBox.php, this draws the boxes, but does not check them. You can somehow extend this.

Finally, you can simply add bunch of Label’s and add button “Edit” which would open modal with crud.

More examples for handling many-to-many could be a good milestone for release 1.5. Also your exact UI can probably also be implemented, but would require a bit of JavaScript.


#3

Hi there,

unfortunately I am stuck at a very basic point, just reading a Many-to-Many relation. I used the database structure from above and manually added a relation from the guest with id 1 to the group with id 1.

I copied the code from this tutorial http://agile-data.readthedocs.io/en/develop/advanced.html?highlight=hasMany#creating-many-to-many-relationship to define the relation between Guests and Groups:

class Group extends \atk4\data\Model {
   
	public $table = 'group';

	function init() {
		parent::init();

		$this->addFields([
			['name', 'type'=>'string'],
			['name_contact', 'type'=>'string'],
			['amount_guests', 'type'=>'integer'],
			['amount_interested', 'type'=>'integer'],
			['standard_email', 'type'=>'email'],
			['standard_phone', 'type'=>'string'],
			['coming_from', 'type'=>'string'],
		]);
		//$this->hasOne('group_origin_id', new GroupOrigin());
		$this->hasMany('GuestToGroup', new GuestToGroup());
		$this->hasMany('Guest', [function($m) {
			$p = new Guest($m->persistence);
			$j = $p->join('guest_to_group.guest_id');
			$j->hasOne('group_id', 'Group');
		}, 'their_field'=>'group_id']);
		
		$this->addHook('beforeDelete',function($m){
			$m->ref('GuestToGroup')->action('delete')->execute();
		});
	}
}



class Guest extends \atk4\data\Model {
   
	public $table = 'guest';

	function init() {
		parent::init();

		$this->addFields([
			['firstname', 'type'=>'string'],
			['lastname', 'type'=>'string'],
			['gender', 'type'=>'string'],
			['birthday', 'type'=>'date'],
			['nickname', 'type'=>'string'],
			['weight', 'type'=>'integer'],
			['height', 'type'=>'integer'],
			['shoesize', 'type'=>'string'],
		]);
		$this->hasMany('GuestToGroup', new GuestToGroup());
		$this->hasMany('Group', [function($m) {
			$p = new Group($m->persistence);
			$j = $p->join('guest_to_group.group_id');
			$j->hasOne('guest_id', 'Guest');
		}, 'their_field'=>'guest_id']);
		
		$this->addHook('beforeDelete',function($m){
			$m->ref('GuestToGroup')->action('delete')->execute();
		});
		//$this->hasMany('Phone', new Phone());
		//$this->hasMany('Email', new Email());
		//$this->hasMany('Address', new Address());
	}
}

class GuestToGroup extends \atk4\data\Model {
	
	public $table='guest_to_group';
	
	function init() {
		parent::init();
		$this->hasOne('guest_id', 'Guest');
		$this->hasOne('group_id', 'Group');
	}
}

Now, I try to load the ref:

require 'lib.php';
$app = new TourManagement();

$guest = new Guest($app->db);
$guest->load(1);
$y = $guest->hasRef('Group');
var_dump($y);
$guest_groups = $guest->ref('Group');

This throws the following exception:

The relation is there, the content of var_dump($y) is:

object(atk4\data\Reference_Many)#179 (3) {
  ["link"]=>
  string(5) "Group"
  ["model"]=>
  object(Closure)#178 (2) {
    ["this"]=>
    object(Guest)#154 (2) {
      ["id"]=>
      string(1) "1"
      ["conditions"]=>
      array(0) {
      }
    }
    ["parameter"]=>
    array(1) {
      ["$m"]=>
      string(10) "<required>"
    }
  }
  ["their_field"]=>
  string(8) "guest_id"
}

Anybody can help me getting a working Many-to-Many relation here?
Thanks alot
Philipp


#4

I have wrote this article, which could be helpful:


#5

Hi Romans,

if you write a complete article each time I ask something there is no need for me to write tutorials :slight_smile:
Thanks for this articles, it seems much easier than the other sources I linked.

Using the ManyToMany article and the AutoComplete field, I was able to create the ManyToMany UI easily. It still looks horrible, but it works: A new ManytoMany relationship can be easily created with the AutoComplete field, existing relations are listed and can be deleted.

It still looks ugly, but it works. Styling comes when all functionality is there.

For others who might want to do this, here is my code so far. Its surely not perfect but a start:

//if Record is loaded, show all associated Guides and add possibility to delete/add new relation
if($tour->loaded()) {
	$guide_form = $right_column->add('Form');
	$guide_form->setModel(new Guide($app->db), false);
	
	//load existing linked Guides
	$guide_ids = $tour->ref('GuideToTour')->export(['guide_id']);
	$guide_ids_related = [];
	//display a label and a delete button (or label in this case) for each Guide
	foreach ($guide_ids as $id) {
		$guide = new Guide($app->db);
		$guide->tryLoad($id['guide_id']);
		if($guide->loaded()) {
			$guide_ids_related[] = intval($id['guide_id']);
			$guide_form->add(['Label', $guide['name']], 'Content')->link('guides.php?id='.$guide['id']);
			$guide_form->add(['Label', 'X'], 'Content')->on('click', function() use ($tour, $app, $guide) {
				$guide->ref('GuideToTour')->action('delete', [$tour['id']])->execute();
				return (new \atk4\ui\jQuery('.pmgcontent'))->trigger('pmgcontentreload');
				},  ['confirm'=>'Are you sure you want to remove '.$guide['name'].' from this tour?']);
		}
	}
	
	//have Autocomplete button to add an existing Guide to the tour or add a new Guide.
	//Guides already related are excluded via addCondition
	$autocomplete_model_guide = new Guide($app->db);
	//Important: Check if array is not empty. Empty Array leads to SQL fail)
	if(count($guide_ids_related) > 0) {
		$autocomplete_model_guide->addCondition('id', 'not in', $guide_ids_related);
	}
	$guide_form->addField('add_guide_to_tour', [
		'AutoComplete',
		'model'			=> $autocomplete_model_guide,
		'placeholder'	=> 'Select Guide',
		'search'		=> ['name'],
	]);
	
	$guide_form->buttonSave->set('Add Guide to this Tour');
	//on Submit: create new GuideToTour Relation
	$guide_form->onSubmit(function($guide_form) use ($app, $tour) {
		if($guide_form->fields['add_guide_to_tour']->getValue() !== null && $tour->get('id') !== null) {
			$gutt = new GuideToTour($app->db);
			$gutt['guide_id'] = $guide_form->fields['add_guide_to_tour']->getValue();
			$gutt['tour_id'] = $tour->get('id');
			$gutt->save();
		}
		else {
			//TODO: Message to user
		}
		return (new \atk4\ui\jQuery('.pmgcontent'))->trigger('pmgcontentreload');
	});
}

Looks still ugly but works:
mtom3

Best regards
Philipp


#6

I think It’s still important that you write tutorial specifically on how you have implemented your View.


#7

Hi Romans,

a good tutorial for a lot of things I am working on is on my ToDo-List :slight_smile: I just don’t want to write tutorials about half-finished tasks. I wrote an outline about some things I would like to write about, will send it to you.

Best Regards
Philipp


#8

Ok, time to show something:

Its still the the MToM Relation from the start post, a group can have many guests and a guest can be in many groups. They are linked using the guest_to_group table / the GuestToGroup model.

See the screencast to see some action:
http://www.spame.de/mtominaction.mkv

As you can see, its easy to add guests to the group or remove a guest from the group. It can not handle multiple adds/deletes, its not needed in my case as a group typically has a max. of 16 guests.

Its these lines of code (i commented it heavily for easier understanding), the rest is some CSS to make it look a bit nicer:

/*
 * Show all Guests registered with this Group if the Group record was loaded
 */
if($group->loaded()) {

	//create new Form for MtoM relation
	$guest_form = new \atk4\ui\Form();
	
	//add to an existing view
	$right_column->add($guest_form);

	//do not display field labels
	$guest_form->layout->inline = true;

	//Add reloader for the form
	$guest_form->addClass('pmgreloadguestchange')->on('pmgreloadguestchange', new \atk4\ui\jsReload($guest_form));

	//Heading
	$guest_form->add(['Text', '<h3>Gäste in dieser Gästegruppe</h3>'], 'AboveFields');

	//The form is submitted as soon as a change occurs, no save button is needed
	$guest_form->buttonSave->destroy();


	$guests = new \Pmg\Guest($app->db);
	
	//load existing linked Guests, returns an array with all associated guest ids
	$ref_ids = $group->getRefGuestIDs();

	/*
	 * if referenced Guests were found, display each as label and add delete label
	 */
	if(count($ref_ids) > 0) {
		$guests->addCondition('id', 'in', $ref_ids);
		$guests->tryLoadAny();

		//for each referenced guest
		foreach($guests as $act_id => $act_guest) {
			//label for guest
			$guest_form->layout->add(['Label', $act_guest['name']])->link('guests.php?id='.$act_guest['id'])->addClass('pmg-mtomitem');
			//label for delete, on click delete the GuestToGroup entry
			$rm = new \atk4\ui\Label(['', 'pmg-mtomdelete', 'icon'=>'remove circle']);
			$guest_form->layout->add($rm)->on('click', function() use ($group, $app, $act_guest) {
				$gutg = new \Pmg\GuestToGroup($app->db);
				$gutg->addCondition('group_id', '=', $group->get('id'));
				$gutg->addCondition('guest_id', '=', $act_guest->get('id'));
				$gutg->tryLoadAny();
				if($gutg->loaded()) {
					$gutg->action('delete')->execute();
				}
				//trigger reload
				return (new \atk4\ui\jQuery('.pmgreloadguestchange'))->trigger('pmgreloadguestchange');
				},  ['confirm'=>'Willst du den Gast '.$act_guest['name'].' wirklich von dieser Gästegruppe löschen?']);
		}
	}
	
	/*
	 * Autocomplete button to add an existing Guest to the Group or add a new Guest.
	 */
	//Guests already related are excluded via addCondition
	$autocomplete_model = new \Pmg\Guest($app->db);
	//Important: Check if array is not empty. Empty Array leads to SQL fail)
	if(count($ref_ids) > 0) {
		$autocomplete_model->addCondition('id', 'not in', $ref_ids);
	}
	//add the Autocomplete
	$r = $guest_form->addField('add_guest_to_group', [
		'AutoComplete',
		'model'			=> $autocomplete_model,
		'placeholder'	=> 'Bestehenden Gast hinzufügen',
		'search'		=> ['name'],
		'plus'			=> true,
	]);
	
	$r->on('change', $guest_form->js()->form('submit'));

	/*
	 * Form Submit function: create new GuestToGroup and store,
	 * reload form to display added guest.
	 */
	$guest_form->onSubmit(function($guest_form) use ($app, $group) {
		if(!empty($guest_form->fields['add_guest_to_group']->getValue())) {
			$gutg = new \Pmg\GuestToGroup($app->db);
			$gutg['guest_id'] = $guest_form->fields['add_guest_to_group']->getValue();
			$gutg['group_id'] = $group->get('id');
			$gutg->save();
		}
		//trigger reload of form
		return (new \atk4\ui\jQuery('.pmgreloadguestchange'))->trigger('pmgreloadguestchange');
	});

Best regards
Philipp


#9

Great start for an amazing add-on :slight_smile:

Edit: here i have converted MKV into a gif, so you can use it in markdown readme file somewhere:

mtominaction