• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Book archive project

Gruber

Member
Hello there!

I'm developing a simple project (I think) for a book archive/catalogue.
The keeper will provide me a .CSV file with the data for every book record, and they can make a new one too based on some specifics request from me (column with specific data formatted in particular way etc..)

I've been asked to make a list with all the books available, another with the authors of the books, and another with the books genres. The title list will show records that directly link to the detail view of the book. Also each book should display a link for its author and genres page result as explained below.

The authors list will show each author as a link. Each author link will lead to a page where all the books of that author are listed. Same logic applies to the genres list.

They also asked me to be able to add and update the archive with new books etc.

I think this is pretty standard and easy to achieve but I'm not sure how to do this.

I've been able to sucessfully create a list with some dummy data for the simple titles list, detail view and so on, and setup correctly a user to add/update this single table records too.
Trying to stick with a single table (since I was more or less able to manage it), I thought that maybe using a tagify element could be a viable quick and dirty solution: I found tho that while in the genres case it's a sort of acceptable workaround to use along with an "adapted" tag cloud module (still it looks rather hacky to me), I saw that in the authors case it's not fitting.

Unfortunately I'm more confident with front end development. I can get my hand dirty on the back end with some PHP and some straight DB queries, but the whole organization of a database, tables relations and so on is a bit off my actual knowledge :(

What is particularly tricky for me to grasp is how to "bind" a book record to an author/genre in Fabrik and how to allow a user to insert a new record with this "binds" in action.
I know the basics on how usually a DB handle this (foreign keys, one/many to many relations and so on). I saw some examples in your documentation about joins elements or the example video in home, but many times the join is based on a prebuild list of things while in my case obviously is not.
I've tried using the databasejoin element with auto-complete field but I can't wrap my head around it: in my case the user should be able to create a new author/genre entries or connect to one already existing in the records. Am I missing something obvious?

So the questions are: how it's (supposedly) the best and simplest way to do this? Is it possible to create a record and bind it to a record in another table without prebuilding it?

I tried to explain myself best I can (english is not my first language), if something is not clear please tell me!

Thank you for any help or assistance!
 
Hey there,
small update, I managed to create a working one-to-many relatio books-author, but I think is a bit counterintuitive (or more probably I'm missing something).

As of now I made a databasejoin element with auto-complete option on. Also I made a concat of "{name} {surname}" since I want it displayed as a single string in the list and pretty much anywhere. Now if I digit something it will check if the record already exists and that's fine, allowing me to chose.
If not it will do nothing. Eventually I found the "add front-end" function and now clicking on the "+" icon a popup form kicks in allowing me to create a new Author record with Name and Surname separated fields.

In this book archive a small part of the books are from the same author, so it's more common to create a new author with each record.
The process gets a bit tedious this way (open the popup, wait for it to load, insert data and save) but fairly acceptable, I encountered a problem tho.

I haven't found an option to allow an update/edit for an already stored record with the databasejoin element. Let's say the user wrong typed either the Name or Surname of the author in the "add book" form.
Clicking on the "edit" button in the list view, the edit form is presented with the databasejoin element prefilled with the wrong typed author. If I press the "+" icon the popup comes in with empty fields allowing me to create a new one, but I can't modify the old one, so basically I leave a wrong record in the table with no books applied to.
How can someone edit/update from the same form a databasejoin element?

Else is it possible to achieve the same thing in a more (arguably) convenient way?
I'd like the user to fill the data straight away (Name and Surname fields).Then if the record is similar/equal to one already stored in the table, a mechanism like the drop down of the auto-complete option kicks in allowing to chose one, else a new record will be created.
If there is a need to modify/update something, the user can do the modification right here in the same form; a message can then be showed like "you want to modify the record or add a new one?".
Is this doable in a reasonable way or it's just not worth the hassle?

Thank you!
 
Apologies for the delay responding, that's a lot to process. I need to read it a few more times to get my head round what you are trying to do.

-- hugh
 
No problem, thank you! I wrote a wall text because I tried to give information the best I can, take your time! ;)
 
No, we don't have a mechanism to edit from a join. One of those things we've been meaning to do, but isn't trivial.

It would help if I could see your site, so if you want to fill out http://fabrikar.com/you/my-sites, I'll poke around and get the flavor of it.

-- hugh
 
I see, unfortunately atm the project is an early local machine stage (the domain is not even purchased yet!).
I'll set up a demo somewhere and fill out the credential asap.
Thank you !
 
You can always do a dynamic DNS, there are a number of free DDNS services out there, and do port forwarding on your router.

Or if you want to go Pro, I don't mind doing TeamViewer sessions, which are quite useful.

-- hugh
 
Hey Hugh!
I've sent you a PM (I think) via "Converations" did you received it or I messed up with something?

Thank you!
 
Hey there, I'm updating this message to say how I've resolved my previous request thanks to Hugh's suggestions, maybe someone will find this useful!

Quick recap: I have a list with 2 joins, one for a 1-to-many relationship (books - author) and another for a many-to-many relationship (books - genres).
The form of this list will be used to create/edit book records. In the same form (via the joins) the user will be able also to insert/edit the author and genres of the book. Also I wanted the user to be able to manage this all from a single "interface", so that within a single form page he can do what normally is achieved by giving acess to 3 different forms.

Now the main problem is allowing the user to insert/update from the same form data into different lists (tables).
Fabrik has two simple buit in methods that allow this partially:
  • one is to use a databasejoin element along with the option "Add option in front end", this will allow to pick an element or create a new one, but not to update an already stored element in another table
  • the other is to directly use the form elements from the joined lists we are interested in, this will allow to directly store new data and edit at a later if needed, but it will always create a new record in the joined table as we created basically a 1-to-1 relationship. We can't pick a previous stored record, and if we fill the same exact data again we will just create a duplicate in that table.
Below I explain how I made the 1-to-many relationship form to work the way I intended (you can check this demo for a live fiddle of the functionality).
  1. First we need to create a databasejoin element in our list representing the "many" part of the relationship (the books list in my case) linked to the list you are interested for the "1" part (in my case the authors list).
  2. Then we setup the join in the main list we are going to work with (the books) and set the databasejoin element as the Foreign Key for the join (the "From column" element).
  3. Thanks to the join we now have the same elements of the authors form as a new group of elements that can be used and inserted inside the books form. We have now the possibility of store data inside the authors list from the books form, this is what we need if we want to create a new author. Tho we will face what I pointed above: leaving the form like this each book will store a new author record (possibly creating duplicates), without any option to select an already existing author if needed . Here's where the DBJ we created earlier kicks in.
  4. The DBJ element is a simple INT field inside our table, it stores the IDs (as FK) of the elements of the other table we join to. Thanks to the funcionality of Fabrik, the DBJ will allow a user to select an item from the other table and store that ID inside our FK field if we need so; if we don't select anything it will remain empty and Fabrik will smartly fill it later when the form is saved with the FK needed. Here's where Hugh's help comes to play: thanks the Autofill form plugin we have the chance to fill the fields we want/need of our author form automatically via ajax. With this we have now the possibility to edit the entry we picked, since we have the correct FK linked to the author table record, and once the form is saved the new data we entered in the text field will be updated accordingly.
This is what we should have by now more or less
JW64k7p.jpg
 
Hi, thanks, it's an excellent idea to use such combination of list join and form autofill :) BTW, that's a good looking alternative to the "description" functionality in databasejoin element.

When speaking about your solution - it seems you presume that a book has always one single author. But what if one book has 2 or more authors?
It's your decision but in your place I'd use a bridge table between books and authors, make another list join to the books list (join settings in books list: from books to bridge from id to fk field) and set it to repeat. Another fk in this books table would be a databasejoin element pointing to the author data. In this case, when you want to display more data about the author than only name you can use the description functionality in dbjoin element that I mentioned above. Perhaps you need also some field for author's roles (writer, translator etc).
Ah, it seems also that when creating a list join, you have set a field in main table as fk and the id in child table as pk. This way works but doesn't allow you make your joined group repeatable.
 
We discussed the one-to-many vs many-to-many thing, he's happy with single author - this a rare, antique book database, which pretty much all have single authors. And doing the one-to-many letsus do the "FK on the parent table" thing, which is what allows doing the autofill trick.

-- hugh
 
Thank you Jaanus for chiming in!
Yes you're right, the m-m relationship is indeed left out this way, but as Hugh pointed out this is "intended", also because the book keepers deal with multiple authors per book using a "Various authors" category (sort of).
The description option for the DBJ element looks interesting but I can't really grasp in full, is it basically an additional sort of "label"?

I'm trying to get this same logic working with a m-m relationship (for the genres of the book) along with a repeat group in the form. I'm "almost" done with it, but I'm struggling updating the data in the table of the repeat group (and I think you already spotted the problem as you said above).
I'm using a third table as a mapper table to correctly link the genres and the books. The table is structured like this (with a sample data for a book):
Code:
+-----------------------+----------------+------------------+--------------+
| mapper_generi_id (PK) | book_id_map_fk | genere_id_map_fk | genre_filler |
+-----------------------+----------------+------------------+--------------+
| 1                     | 1              | 1                | History      |
+-----------------------+----------------+------------------+--------------+
| 2                     | 1              | 2                | Geography    |
+-----------------------+----------------+------------------+--------------+
The "genre_filler" is a dummy column I placed so that I have a field form to fill with the autofill form plugin.
Like in the author actual logic, if you don't select an already stored genre, you can create and fill a new one right away or update it if needed at a later time.

To make this work I used the php script plugin for the form loaded onBeforeStore with this code:
PHP:
/**
* Setup
*/

// get the DB
$db = JFactory::getDbo();
$query = $db->getQuery(true);

// get the data value of the DBJ element storing the FK id in the mapper table
$genreFkId = $formModel->getElementData('lra_generi_books_mapper___genere_id_map_fk', true);

/**
* Create a new record in the genre mapper table
*/

// get the value inserted in the genre "filler" field
$genreFiller = $formModel->getElementData('lra_generi_books_mapper___genre_filler', true);

foreach ($genreFiller as $key => $genre) {
  // check if the DBJ is empty, if it's empty this means no genre already stored is needed, 
  // so we create a new genre for the book
  if (!$genreFkId[$key][0]) {
    // first we create a new genre record in the genre table
    try {
      // create a new object on the fly that will keep the data
      $newGenre = new stdClass();
      //store the data we want to insert in the table
      $newGenre->genere = $genre;
      $newGenre->date_time = JFactory::getDate()->toSql(); //time of inserting
      // insert in the genre table
      $db->insertObject('lra_generi_books', $newGenre);

      // then we retrieve this new inserted genre ID
      $genreId = $db->insertid();
      // and update the form data for the DBJ element value
      $formModel->updateFormData('lra_generi_books_mapper___genere_id_map_fk', $genreId, true);

    } catch (Exception $e) {
      dump($e->getMessage(), 'error');
    }
  }
}

The above code kinda works, but I'm stuck with the last function call updateFormData.
The genre is correctly stored in the genres table, and this last created record id is collected by the query. Trying to use updateFormData the data is overwritten at every step of the loop only for the top most record in the repeatgroup with the last inserted genre, the other are simply left blank (below example record of wrong stored values in the mapper table)
Code:
+-----------------------+----------------+------------------+--------------+
| mapper_generi_id (PK) | book_id_map_fk | genere_id_map_fk | genre_filler |
+-----------------------+----------------+------------------+--------------+
| 1                     | 1              | 3                | History      |
+-----------------------+----------------+------------------+--------------+
| 2                     | 1              | null             | Geography    |
+-----------------------+----------------+------------------+--------------+
| 3                     | 1              | null             | Religion     |
+-----------------------+----------------+------------------+--------------+
That 3 ID value in the book_id_map_fk column corresponds in reality to the Religion genre ID, but is wrongly mapped due to this override. What am I missing?

I was thinking also that maybe I could update directly the mapper table via a WHERE query and relieve Fabrik of this job, but I hope there is some inner way of doing this without me much messing with the DB :p . What you think? (btw sorry for my wall texts :| )
 
Last edited:
Yes. Because it's a fair amount of work to do, which we haven't done yet, because nobody has asked for it. Or at least not enough people, or loudly enough. :)
-- hugh
FYI - using frontend select allows editing the existing data!
 
Yup, but it's a little more cumbersome. It would be nice to have a simple "Edit" for the currently selected option.

-- hugh
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top