The Joomla subform field type has many uses. For xbBooks and related components I needed to populate a link table joining books and people (people may be authors, editors, mentioned in a book, or fictional characters in a book). Since a person may take on more than one role for a particular book, or between books, the link table needs to have an additional field to hold what role the link represents as well as the two id fields: book_id and person_id.

From the admin point of view the user needs to either be able to specify who the author(s) (a book may have several authors) are when creating/editing the book entry, or be able to specify what books the person is associated with when creating/editing the person entry.

Subfrom fields make this a doddle.

So I have three tables - #__xbbooks which has an id field and other details of the book, #__xbpersons which also has an id field, and #__xbbookperson which is the link field with five fields: id, book_id, person_id, role, listorder. That last one, listorder, is so that all the people having the same role in a book can be sorted into a default order for display - eg the lead characters might be listed before more minor ones, the subject of a biography might be listed before the other people mentioned in the book.

So taking the book edit page as an example, as well as all the other fields for the book we have a subform for authors which will be populated by any existing entries for author for the book and will update the #__xbbookperson table once the rest of the data is accepted into the #__xbbooks table.

Thus in the form book.xml we have

<?xml version="1.0" encoding="UTF-8"?>
<field name= "authorlist"
	type= "subform"
	label= "COM_XBBOOKS_FIELD_AUTHOR_LABEL"
	description="COM_XBBOOKS_FIELD_AUTHOR_DESC"
	min= "0"
	max= "10"
	multiple= "true"
	buttons= "add,remove,move"
	layout="joomla.form.field.subform.repeatable-table"
	groupByFieldset="false" >
	<form>
		<field name="person_id" type="people" label="Select author(s)" >
			<option value="">JSELECT</option>	
		</field>
	</form>
</field>

 The subform is set to allow multiple selection and to have buttons for adding, deleting, and moving (to change the listorder) rows.

The source form for each row uses a custom field type to provide a list of people. 

The custom field looks like this:

<?php
defined('JPATH_BASE') or die;

JFormHelper::loadFieldClass('list');

class JFormFieldPeople extends JFormFieldList {   
    protected $type = 'People';   
    public function getOptions() {      
        $options = array();       
        $db = JFactory::getDbo();
        $query  = $db->getQuery(true);       
        $query->select('id As value')
            ->select('CONCAT(firstname, " ", lastname, " (", state, ")") AS text') //switch name order if you wish
            ->from('#__xbpersons')
            ->where('state=1')  //remove this if we want to include unpublished/trashed people in the list
            ->order('lastname'); //or firstname       
        $db->setQuery($query);
        $options = $db->loadObjectList();
        // Merge any additional options in the XML definition.
        $options = array_merge(parent::getOptions(), $options);
        return $options;
    }
}

 In order to load the subform with any existing data we need to query the #__xbbookperson table for any rows which match the id of the current book being edited. Do this in the model script by adding a loadFormData function:

    protected function loadFormData() {
        $data = Factory::getApplication()->getUserState('com_xbbooks.edit.book.data', array() );
        
        if (empty($data)) {
            $data = $this->getItem();          
	        $data->authorlist=$this->getBookPeoplelist('author');
        }             
        return $data;
    }

    

 The getBookPeopleList() function takes the role as a parameter and is also used to populate other subforms for different roles. It returns an assoc array which Joomla can use directly to populate the subform. So loadFormData() just needs to add an element to the form data containing the data to be loaded into the subform.

    function getBookPeoplelist($role) {
            $db = $this->getDbo();
            $query = $db->getQuery(true);
            $query->select('a.id as person_id');
            $query->from('#__xbbookperson AS ba');
            $query->innerjoin('#__xbpersons AS a ON ba.person_id = a.id');
            $query->where('ba.book_id = '.(int) $this->getItem()->id);
            $query->where('ba.role = "'.$role.'"');
            $query->order('ba.listorder ASC');
            $db->setQuery($query);
            return $db->loadAssocList();
    }

 When the edit form is saved then the new data in the subform has to be written out to the #__xbbookperson table. Rather than try and detect any changes and delete any individual items where the person has been removed and only add new items that are not already in the table, it is far easier and quicker to simply delete all of the author person entries for the book and then write out the subform data complete. The only side effect is that the ids for the rows in the table (which are auto-increment integer fields) will change, but the id field in #__xbbookperson is only there to provide a simple primary key for mysql, and is never used in the code. 

In the model you need to add a function to the save($data) function to save the subform data.

    public function save($data) {
        $input = Factory::getApplication()->input;
        
        // do other stuff as required (eg save2copy task handling)
        
        if (parent::save($data)) {
            $this->storeBookPersons($this->getState('book.id'),'author', $data['authorlist']);
            $this->storeBookPersons($this->getState('book.id'),'editor', $data['editorlist']);
            $this->storeBookPersons($this->getState('book.id'),'character', $data['charlist']);
            return true;
        }
        
        return false;
    }

 storeBookPersons() is written as a separate function as it is needed for each of the subforms (in xbbooks there are separate subforms for adding authors, editors, fictional characters, and real people - all of whom are stored in the #__xbpersons table. The same #__xbpersons table is also used by xbFilms and other components that might appear (xbGigs, xbPlays, xbAlbums...). A person might be an actor who appears in plays and films and also writes books and is the subject of a biography, not to mention the band he banjo in...

    function storeBookPersons($book_id, $role, $personList) {
        //delete existing role list
        $db = $this->getDbo();
        $query = $db->getQuery(true);
        $query->delete($db->quoteName('#__xbbookperson'));
        $query->where('book_id = '.$book_id.' AND role = "'.$role.'"');
        $db->setQuery($query);
        $db->execute();
        //restore the new list
         foreach ($personList as $pers) {
            $query = $db->getQuery(true);
            $query->insert($db->quoteName('#__xbbookperson'));
            $query->columns('book_id,person_id,role,listorder');
            $query->values('"'.$book_id.'","'.$pers['person_id'].'","'.$role.'","'.$pers['order'].'"');
            $db->setQuery($query);
            $db->execute();            
        }
    }

 And that's about all there is to it. Joomla subforms use associated arrays of data - if you want to save the data simply as a json string in one table then it is a bit simpler, but that is no good for link tables.