Database wise: can I say Joomla subform is the replacement of a link table?

[ni...@nicovandekamp.nl]

Hello,

I'm not DBA'er or that kind of developer. I have learned SQL (PL-SQL) and I grow up with link tables with the fields:

• id
• id_table1
• id_table2

I have made recent a screen in Joomla backend with his own save functions in the controller/model file's to save the data in two or even more tables. This is al working, some people help me further on this forum.

Now I understand better the subform and these data is saved in basic table as JSON string like {id, id_table1, id_table2}, {...}.

To me, and I'm asking this myself, it looks that the subform is a replacement for the link table. Is that right?

And what I'm asking myself if that is right, are there some disadvantages to use that database field as link table? Or Is better to use a link table, for example from performance perspective?

I don't know if this is a stupid question but this what is going round in my mind. I see more advantages to use it like to make an export to Excel of the data for example. It is one table!

Nico

[ni...@nicovandekamp.nl]

I forgot one important thing to mention, I'm using a custom field like categoryedit.php. This is dropdown with the id as value and a field (or concatenate fields) as the list value to user. So in the subform db field is the id saved of the related table in the record were it is related to.

[Hannes Papenberg]

No, subforms are not a replacement for a n:m mapping table. Storing them as JSON means, that you can not query/search that data from the database, can't add indexes or similar stuff. You can use subforms and save that data in a n:m mapping table, but storing that as JSON is a bad idea and you should have very good reasons to do that.

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/joomla-dev-general/e99b87d9-7310-4c13-9d5c-17109adbed88n%40googlegroups.com.

[Roger Creagh]

On Mon, 2020-09-21 at 22:12 +0200, 'Hannes Papenberg' via Joomla! General Development wrote:

No, subforms are not a replacement for a n:m mapping table. Storing them as JSON means, that you can not query/search that data from the database, can't add indexes or similar stuff. You can use subforms and save that data in a n:m mapping table, but storing that as JSON is a bad idea and you should have very good reasons to do that.

So just to be totally clear, as Hannes mentions in his last line, subforms are ideal for inputting data for a many-to-many link table. What you need to do is write the data out to the link table, not to a field in a table as a json.

For example suppose you have a database containing a table for books, a table for authors, and a link table bookauthor to enable a many to many relationship (a book may have several authors, an author may contribute to several books)

In the book edit form you want to be able to assign authors to the book.

So create a subform named authorlist in the book edit form which enables selection of multiple names from the authors table and returns the selected ids.

<field name= "authorlist"

type= "subform"

label= "Authors"

description="Select authors for this book"

min= "0"

max= "10"

formsource= "administrator/components/com_yourcomponent/models/forms/peoplelist.xml"

multiple= "true"

buttons= "add,remove,move"

layout="joomla.form.field.subform.repeatable-table"

groupByFieldset="false" 

/>

The formsource for the author list might be a simple sql field getting the names (AS text) and ids (AS value) for the list, or it might be a custom field.

In the model for  book edit create (or add to) the loadFormData() function like this:

protected function loadFormData() {

        $data = Factory::getApplication()->getUserState('com_yourcomponent.edit.book.data', array() );        

        if (empty($data)) {

             $data = $this->getItem();          

        $data->authorlist=$this->getBookAuthorlist();

        }             

        return $data;

    }

function getBookAuthorlist() {

            $db = $this->getDbo();

            $query = $db->getQuery(true);

            $query->select('a.id as author_id');

            $query->from('#__bookauthorAS ba');

            $query->innerjoin('#__authors AS a ON ba.author_id = a.id');

            $query->where('ba.book_id = '.(int) $this->getItem()->id);

            $query->order('ba.listorder ASC');

            $db->setQuery($query);

            return $db->loadAssocList();

    }

This will load any existing authors into the subform if you are editing an existing item.

Then in the model save($data) function you simply need to update the link table once the parent::save($data) function has returned ok.

The quirk here is that since authors may have been deleted as well as added during the edit you need to start by deleting all of the existing authors for the book in the bookperson table and then add back all of the selected ones.

        $book_id = $this->getState('book.id');

$personList=$data['authorlist'];

$db = $this->getDbo();

//delete any existing entries for this book

        $query = $db->getQuery(true);

        $query->delete($db->quoteName('#__bookauthor'));

        $query->where('book_id = '.$book_id);

        $db->setQuery($query);

        $db->execute();

        //restore the new list

         foreach ($personList as $pers) {

            $query = $db->getQuery(true);

            $query->insert($db->quoteName('#__bookauthor'));

            $query->columns('book_id, person_id, listorder');

            $query->values('"'.$book_id.'","'.$pers['person_id'].'","'.$pers['order'].'"');

            $db->setQuery($query);

            $db->execute();

            $order++            

        }

NB I am also including an 'listorder' column in the link table so that authors for a book can be listed in a specific order - sometimes the lead author needs to be listed first. The subform with move button will generate an order field.

It is all quite simple once you try it. Of course for user convenience you will want to have a corresponding subform on the author edit page to assign an author to a book, otherwise you have to remember to create the author entry before you edit the book and vice versa!

Hope this helps (and hope it is right!!)

RogerCO

[ni...@nicovandekamp.nl]

Hello Hannes and Roger,

Thanks, I understand what you are both saying and I'm agree. I thought to easy in the hope that it make it simple for me.

I have created a multtab edit.php with a filterbar, and all records with let say the for example 'autors', 300 rows (=records). Every row has a checkbox to select one or more authers. I have made save function in the controller/model to save the data for base table and the mapping/linking table. This gives issues and had to increase the php.ini parameter "max_input_vars" form 1000 to 10000.  

But this gives me an bad feeling.

Then I discovered still better the subform how it works and in combination with customfield like 'categoryedit' which is a dropdown (with all the authors) + search facility makes it a lot easier to save and to load it in the subform again.

But I will use the subform and than in my save function convert the subform data to the mapping table, which is not an issue for me. Adding authors and removing authors is not issue for me because I'm doing this at the moment also.The other side to load the data again into the subform (edit.php) is new for me.

But the subform is a big advantage, working nicely and is solving the php.ini "max_input_vars" issue for me!

Thanks

Nico