I'd like to share my experience with you and BigProf to bring forward this feature request. I've done different workarounds with hooks for multiselect relations as you can see in this article...
viewtopic.php?f=8&t=10&p=8956&hilit=multi#p8956
- 2018-10-17_20-56-51.gif (55.79 KiB) Viewed 15381 times
... AND ...
...another solution, which is the version I'm currently using in my projects. Have a look at the following screencast:
- 2019-07-01_20-33-31.gif (167.81 KiB) Viewed 15381 times
This is not only multi select options list but data from different tables. Evertything has been done with hooks. The assignments are being saved in a relationship-table on saving the record itself.
Although I already have a working solution for my projects and customers, I'd really like to have it in AppGini standard. So
+1 for this feature request!
Ahmed's article mentioned before is a really good starting point. A widely used example is the bookstore-example. A book can have multiple authors, and an author can write multiple books. Next to "books" and "authors" tables...
books
id
name
authors
id
name
...you will need a third table "books_authors" for example.
books_authors
id
book_id
author_id
In details view of a
book you need an input field showing the related authors and a dropdown list suggesting the non-related authors. On saving a book, the non-related authors have to be removed from books_authors, and new authors have to be inserted in books_authors-table.
In details view of an
author you need an input field showing the related books and a dropdown list suggesting the non-related books. On saving the author, the non-related books have to be removed from books_authors, and new books have to be inserted in books_authors-table.
I know by my own experience that it will be quite a lot of work for BigProf to implement that into AppGini User Interface. Those inputs have to work in two directions, for example in author detail view you need assignment of books and in book detail view you need assignment of authors. BigProf will have to consider:
- table-name for the relation-table (here:books_authors)
- primary key of the relation-table (here: books_authors.id)
- foreign key column name in the relation-table pointing to the left table (book_id or author_id)
- foreign key column name in the relation-table pointing to the right table (author_id or book_id)
- table-name for the left table (books or authors)
- table-name for the right table (authors or books)
- primary key of the left table (books.id or authors.id)
- primary key of the right table (authors.id or books.id)
- display column(s) for the left table (I only support one column in my solution)
- display column(s) for the right table (I only support one column in my solution)
- filter criteria to show only certain items of the left table in the dropdown (I did not implement this in my solution)
- filter criteria to show only certain items of the right table in the dropdown (I did not implement this in my solution)
- helptext for the field in DV of left table
- helptext for the field in DV of right table
Additionally, if your relation-table has columns like "created_by" or "created_on", they will have to be populated, too, as soon as you have added a relation.
Putting it altogether, it's a lot of work and scenarios to consider. It would push AppGini forward, sure, but only if it works 100%. To be honest, this is a hard task having it in standard fulfulling many of our wishes!
In my implementation there is only little code in my TABLENAME.php hooks file...
Code: Select all
// for setup of a relationship
function wirtschaftseinheit_flurstuecke()
{
$multiselect = new \bizzworxx\ManyToManyRelation('wirtschaftseinheit_flurstuecke', 'id', 'wirtschaftseinheit_id', 'Flurstücke');
$multiselect->setRight('flurstueck_id', 'flurstuecke', 'id', 'nummer');
$multiselect->createdBy = "createdby";
$multiselect->createdOn = "createdon";
return $multiselect;
}
// for rendering the input field and dropdown in DV
function wirtschaftseinheiten_dv($selectedID, $memberInfo, &$html, &$args)
{
$flurstuecke = wirtschaftseinheit_flurstuecke();
$html .= $flurstuecke->render($selectedID);
}
// and for update...
function wirtschaftseinheiten_before_update(&$data, $memberInfo, &$args)
{
$flurstuecke = wirtschaftseinheit_flurstuecke();
return $flurstuecke->update($data, $memberInfo, $args);
}
...but you can imagine there is a lot of PHP-code and JavaScript/JQuery (TypeScript in my case) behind the scene.
I hope my explanations will help pushing this requirement into AppGini 6 or so.
Kind Regards,
Jan