Finally we will build a real Joomla component for our example event schedule! We’ll explore how to implement relationships between entities. 

Small detour

Writing about the implementation of relationships between entities took more space than planned. That’s why I split this episode into two parts. Next month, in the October issue of the magazine, we’ll look at some built-in bells and whistles of Joomla that we can use to enhance our example component and we’ll show some smaller tools that can be of help. Component Creator will be a month later than originally scheduled and will be in the spotlights in the November issue.

Our journey until now

In episode one we introduced an example: an event schedule, showing time-schedules per day of for instance a conference, a festival, a school or whatever. We laid out the entities of our example:

• A page with a schedule/program: a Container.
• One or more “tracks” to schedule events: a Section (multiple sections can schedule events simultaneously, at the same time).
• Something that happens, for instance a presentation: an Event.
• The one who does the event, for instance a speaker or a band: an Actor.

We implemented the example using core features, especially additional fields ⁽¹⁾.

In episode two we worked out the example a bit further and mentioned the many-to-many relationship between events and actors. We also introduced value objects besides entities, specifically:

• An object, which defines where and when an event takes place: a Locator.

The Entity Relationship Diagram of our example event schedule then looked like this:

We implemented this example in the second episode as an “embedded application” in Seblod and Fabrik.

How to build a component?

So now we are going to build a Joomla component for our example. You’ll have to know a bit of PHP. If you’re new to PHP you could for instance have a look here: https://www.w3schools.com/php/. Then read some Joomla-specific information:

• Joomla! Programmers Documentation, our ever expanding online source of information.
• Book: “Developing Extensions for Joomla! 5” by Carlos Cámara.

For more resources, look at the links under this article.

We won’t repeat everything here, this article is too short to be a complete tutorial, but will summarise the main steps of how I built this component. In this article I will elaborate on the implementation of relationships between entities, as you won’t systematically find much on that in the documentation at the moment. We'll “manually” build our component. In coming episodes we’ll look at some tools that can help you build your own component and will even see some tools that can generate a complete component for you.

1. Bare entities, Model-View-Controller

For every entity in our example, create (in the administrator part):

• A table in the database, for instance #__eventschedule_events to store the event-entities in the database. You'll can see which tables and which fields the component creates in the /sql folder under the administrator-part of the component.
• A Table-object, for instance EventTable for an event-entity. This is the object to create, read, update or delete (CRUD) an event.
• An XML-file to define the input-form per entity with all the fields of that entity in the /forms directory. We will implement the (embedded) locator-object as a subform of the event-form.
• A singular and a plural Model, View and Controller (MVC). For events in our example: EventModel, EventView and EventController for the interaction with a single event and an EventsModel, EventsView and EventsController to deal with lists of events. Each view also needs a template layout file under the /tmpl directory.

We add some general files (a manifest, a service-provider file, and access.xml) and the basic backend is ready. All entities can now be input or edited, but not yet the relations between the entities (we’ll do that in step 2).

The setup of this first phase contains quite some boilerplate code. Most of it is almost the same for all entities. We will show some tools to help with that in the coming episodes.

2. Implementing relationships

We’ll have to take care of all the relationships between our entities. Basically there are 3 kinds of relationships between objects:

• One-to-one (1:1). We don’t have a 1:1 relationship in our example component. In Joomla core we have it with the #__content_rating and the #__content_frontpage tables, both with content_id as primary key.
• Many-to-one (n:1). For instance an event can only be of one event type, but an event type can be used for multiple events.
• Many-to-many (n:n). We talked about that in the last episode. In our example for instance between events and actors: an event can be done by multiple actors and an actor can do multiple events. Such a relation needs a junction table. 

A kind of “one-to-many” road sign

In databases a relation between tables is defined with a “foreign key”, which refers to the id of another table. For example when we have an event with an event_type_id column, then the value of that column is the id of an event type in the event type table.

With 1:1 and n:1 relationships the foreign key is always in one table; this is called the “owning side”. For an n:1 relation the owning side is always the table where you only have to store one value, not a collection. Our event can only be of one event type, so the event table is the owning side and stores the event type foreign key. If you would store the relation on the other side, you’d have to store a collection of event identities. In a “normalised” database ⁽²⁾ you don’t store a collection in one field, so we always store the id on the owning side. In a n:n relation the junction table is the owning side.

The “reverse side” of a relation is the side where the foreign key is not stored. In a n:1 and n:n relation the reverse side is a collection. That collection has to be retrieved from the database using a query. 

“ A query walks into a bar and sees two tables. 
   He walks up to them  and says: “Can I join you?”

In core Joomla we don’t use an Object Relational Mapper (ORM) ⁽³⁾ to map the database tables and the objects in our program, so we have to define the relations between the objects ourselves. In Joomla creating, reading, updating and deleting (CRUD) entities is basically done by the Table-object. That is also where the Joomla core handles relations ⁽⁴⁾, for instance the mapping of user groups to users in com_users. This is called an “active record” pattern: the properties of the Table-objects correspond more or less directly to the columns in the underlying database table while retrieving, saving and deleting those records in the database is also done by that Table-object. 

In Joomla the data for list views are provided by models that directly query the database. Those list views don’t use the active record pattern, for they don’t use Table-objects (entities) but directly query the data for the list from the database using the getListQuery() method.

In Joomla we can use a subform-field, which can be used repeatedly. This is not stored in the database in a normalised way, using a single value in a field, but compacted in a JSON-string. We’ll use that for our locator-object. I advise you to put entities (with a separate id) as much as possible in normalised database tables and only use subforms for values that are not used outside the entities to which they belong.

 

---

---

 

To edit actors of an event we add an actor-ids field to the EventTable:

/**
 * @var null|array of integers: the ids of the actors for this event
 * (only used when editing an event)
 */
 private $actor_ids = null;

Bind the $actor_ids from the incoming form-data:

/**
 * Method to bind the event and actors data.
 *
 * @param   array  $array   The data to bind.
 * @param   mixed  $ignore  An array or space separated list of fields to ignore.
 *
 * @return  boolean  True on success, false on failure.
 */
 public function bind($array, $ignore = ''):bool
 {
    // Attempt to bind the data.
    $return = parent::bind($array, $ignore);

    // Set this->actor_ids
    if ($return && array_key_exists('actor_ids', $array)) {
       $this->actor_ids = $array[actor_ids'];
    }
 
    return $return;
 }

 

Store the actors in the junction table. It is done in exactly this way with user groups in the User Table-object in core Joomla (below is a copy of that code, only adjusted for actors instead of user groups) with the following steps:

1. Store  the event-object
2. Get the actors as stored in the database junction table and compare with the selection of actors from the input-form.
3. Delete what is not in the form anymore.
4. Insert the new actor_ids.

/**
 * Method to store a row in the database from the event Table instance properties.
 *
 * If a primary key value is set the row with that primary key value will be updated with the instance property values.
 * If no primary key value is set a new row will be inserted into the database with the properties from the Table instance.
 *
 * The actors for this event will be updated (via a junction table)
 *
 * @param   boolean  $updateNulls  True to update fields even if they are null.
 *
 * @return  boolean  True on success.
 *
 * @see     \Joomla\CMS\Table\User handling of user groups
 */
 public function store($updateNulls = true):bool
 {
    // -- Initialisation --

    // Get the table key and key value.
    $k   = $this->_tbl_key;
    $key = $this->$k;

    // Joomla core comment: 
    // @todo: This is a dumb way to handle the groups.

    // Store actorIds locally so as to not update directly.
    $actorIds = $this->actor_ids;
    unset($this->actor_ids);

    // -- 1. Store the event --

    // Insert or update the object based on the primary key value > 0.
    if ($key) {
        // Already have a table key, update the row.
        $this->_db->updateObject($this->_tbl, $this, $this->_tbl_key, $updateNulls);
    } else {
        // Don't have a table key, insert the row.
        $this->_db->insertObject($this->_tbl, $this, $this->_tbl_key);
    }

    // Reset actor_ids to the local object.
    $this->actor_ids = $actorIds;

    // -- 2a. Get the actors as stored in the database junction table --
 
    $query = $this->_db->getQuery(true);

    // Store the actorId data if the event data was saved.
    if (\is_array($this->actor_ids) && \count($this->actor_ids)) {
        $eventId = (int) $this->id;

        // Grab all actor_ids for the event, as is stored in the junction table
        $query->clear()
            ->select($this->_db->quoteName('actor_id'))
            ->from($this->_db->quoteName('#__eventschedule_actor_event'))
            ->where($this->_db->quoteName('event_id') . ' = :eventid')
            ->order($this->_db->quoteName('actor_id') . ' ASC')
            ->bind(':eventid', $eventId, ParameterType::INTEGER);

        $this->_db->setQuery($query);
        $actorIdsInDb = $this->_db->loadColumn();

        // -- 2b. compare with the selection of actors from the input-form --

        // Loop through them and check if database contains something $this->actor_ids does not
        if (\count($actorIdsInDb)) {
            $deleteActorIds = [];

            foreach ($actorIdsInDb as $storedActorId) {
                if (\in_array($storedActorId, $this->actor_ids)) {
                    // It already exists, no action required
                    unset($actorIds[$storedActorId]);
                } else {
                    $deleteActorIds[] = (int) $storedActorId;
                }
            }

            // -- 3. Delete what is not in the form anymore --

            if (\count($deleteActorIds)) {
                $query->clear()
                    ->delete($this->_db->quoteName('#__eventschedule_actor_event'))
                    ->where($this->_db->quoteName('event_id') . ' = :eventId')
                    ->whereIn($this->_db->quoteName('actor_id'), $deleteActorIds)
                    ->bind(':eventId', $eventId, ParameterType::INTEGER);

                $this->_db->setQuery($query);
                $this->_db->execute();
            }
          
            unset($deleteActorIds);
        }

        // -- 4. Insert the new actor_ ids --

        // If there is anything left in $actorIds it needs to be inserted
        if (\count($actorIds)) {
            // Set the new event actorIds in the db junction table.
            $query->clear()
                ->insert($this->_db->quoteName('#__eventschedule_actor_event'))
                ->columns([$this->_db->quoteName('event_id'), $this->_db->quoteName('actor_id')]);

            foreach ($actorIds as $actorId) {
                $query->values(
                    implode(
                        ',',
                        $query->bindArray(
                            [$this->id , $actorId],
                            [ParameterType::INTEGER, ParameterType::INTEGER]
                        )
                    )
                );
            }

            $this->_db->setQuery($query);
            $this->_db->execute();
        }

        unset($actorIds);
     }

   return true;
 }

 

And delete those actor-ids from the junction table when the event is deleted:

/**
 * Method to delete an event (and mappings of that event to actors) from the database.
 *
 * @param   integer  $eventId  An optional event id.
 *
 * @return  boolean  True on success, false on failure.
 *
 * @see     \Joomla\CMS\Table\User handling of user groups
 */
 public function delete($eventId = null):bool
 {
    // Set the primary key to delete.
    $k = $this->_tbl_key;
 
    if ($eventId) {
      
        $this->$k = (int) $eventId;
    }

    $key = (int) $this->$k;

    // Delete the corresponding actors from the actor-event junction table.
    $query = $this->_db->getQuery(true)
        ->delete($this->_db->quoteName('#__eventschedule_actor_event'))
        ->where($this->_db->quoteName('event_id') . ' = :key')
        ->bind(':key', $key, ParameterType::INTEGER);
    $this->_db->setQuery($query);
    $this->_db->execute();

    // Delete the event.
    $query->clear()
        ->delete($this->_db->quoteName($this->_tbl))
        ->where($this->_db->quoteName($this->_tbl_key) . ' = :key')
        ->bind(':key', $key, ParameterType::INTEGER);
    $this->_db->setQuery($query);
    $this->_db->execute();

    return true;
 }

 

In the EventModel we add a method to retrieve those actor-ids from the junction-table:

    /**
     * Get the actor_ids for this event.
     * @param int|null $event_id
     * @return array
     */
    public function getActorIds(int $event_id = null):array
    {
        $db    = $this->getDatabase();
        $query = $db->getQuery(true)
            ->select($db->quoteName('actor_id'))
            ->from($db->quoteName('#__eventschedule_actor_event', 'junction'))
            ->where($db->quoteName('event_id') . ' = :thisId')
            ->order($db->quoteName('actor_id') . ' ASC')
            ->bind(':thisId', $event_id, ParameterType::INTEGER);

        $actor_ids = $db->setQuery($query)->loadColumn() ?: [];      

        return $actor_ids;
    }

 

In the EventModel we add the actor_ids in loadFormData():

	/**
	 * Method to get the data that should be injected in the form.
	 *
	 * @return  mixed  The data for the form.
	 */
	protected function loadFormData()
	{
		$app = Factory::getApplication();

		// Check the session for previously entered form data.
		$data = $app->getUserState('com_eventschedule.edit.event.data', []);

		if (empty($data))
		{
			$data = $this->getItem();

            // Add foreign key ids
            $data->actor_ids = $this->getActorIds($data->id);
		}

		return $data;
	}

 

This is the sql-field in the event form to get all actors (id + name) to select from:

<field
       name="actor_ids"
       type="sql"
       query="SELECT id, `actor_name` FROM `#__eventschedule_actors`"
       multiple="multiple"
       header="COM_EVENTSCHEDULE_EVENT_FIELD_ACTORS_SELECT_HEADER"
       key_field="id"
       value_field="name"
       label="COM_EVENTSCHEDULE_EVENT_FIELD_ACTORS_LABEL"
       description="COM_EVENTSCHEDULE_EVENT_FIELD_ACTORS_DESC"
/>

 

3. Cascading dynamic select

In our component we have 2 many-to-many relationships:

• Between actors and events.
• Between containers and sections.

After we have set that we are going to schedule the events by placing them in a container and section and giving them a start time (end time could be automatically calculated from start time + duration). In the example of the JoomlaDagen we had the same rooms (sections) available on both days (containers). But it is setup in such a way, that that is not necessarily the case: some sections could only be available in specific containers.

After choosing a container we would only like to see the available sections. So, the choice of the container filters the dropdown for the sections.This is called a "cascading select".

That can be implemented in several ways:

• preparing a section-dropdown for every choice of container and only showing the section-dropdown that corresponds with the chosen container. With form fields that can be easily done using the "showon" attribute.
• make an Ajax-call to the server when the choice is made for the container and dynamically put the right options into the section-dropdown. For me this is the most obvious solution.
• or refresh the whole page after chosing the container, preserving that choice and adjusting the section-dropdown. Because this last option is well documented in the sql form field developers documentation, under "Linked Fields as Filters", I went for this one this time. You can download the example com_sqlfield to see it all working.

A difference with our event schedule example is that our relation between containers and sections is n:n. That is why the query in the sql-field must be joined with the junction table. My section dropdown field in the locator-subform looks like this (mind the sql_join attribute):

 <field
            name="section_id"
            type="sql"
            sql_select="section.id, section.section_name"
            sql_from="#__eventschedule_container_section AS junction"
            sql_join="#__eventschedule_sections AS section ON section.id=junction.section_id"
            sql_order="section.ordering ASC"
            sql_filter="container_id"
            sql_default_container_id="0"
            header="COM_EVENTSCHEDULE_LOCATOR_FIELD_SECTION_SELECT_HEADER"
            key_field="id"
            value_field="section_name"
            label="COM_EVENTSCHEDULE_LOCATOR_FIELD_SECTION_LABEL"
            description="COM_EVENTSCHEDULE_LOCATOR_FIELD_SECTION_DESC"
            context="eventschedule"
    >

Here we select all sections that are in a specific container (as is indicated by the sql_filter attribute). After a container is selected, the form is submitted and the page is newly rendered using the value of the container dropdown to set the sections to choose from.

4. Frontend: the schedule

In episode one we showed an event schedule based on articles + additional fields. The layout templates we now use in our own component are essentially the same as our layout overrides in that first episode. But now we can prepare all variables in the model, avoiding programming logic in templates.

In the frontend we mainly need the schedule view for our example, which is a kind of special list view for our events. This view also needs a controller, a model and a template layout file (including an xml-file to refer to this view in a menu-item). We also make a frontend view with all information of a single event and a list-view of authors.

Last but not least: we make a css-file for our frontend-styling and put it under /media for this component. If we need any javascript or general images for our component, we also put it here.

You can see the complete component we made so far in the repository for this series. Next episode we will take a closer look at the frontend. No room left now, as the detour about relationships took most of our space.

Next month

Adding built-in bells & whistles
Joomla has a lot of  built-in features that we can use relatively easily in our custom components. In this episode we added language strings to our basic component. But there is much more, like general parameters, item ordering, alias handling, locking, filters & search, list-table column ordering, trash, publish and workflow, batch processing, categories, tags, pagination, form validation, ACL, additional fields, multilingual association, hide table columns, versioning, adding custom toolbar buttons, hits, rating, router & SEF URLs, metadata, email cloak, finder, extra views with modules, extensions of the component with plugins, API/web services and CLI. We will expand our example component with some useful features.

Tools that can help
Next episode we will also look at some smaller tools that can be helpful in developing an extension, like create-joomla-extension,  Jext and JoRobo. Originally we planned to showcase Component Creator next month, but that is postponed a month to the November edition of our Magazine. Stay tuned!

 

Notes

1.  In episode one an explanation was given why I use the term “additional fields” instead of  “custom fields”.
2. For an introduction to normalisation of relational databases, see for instance https://www.datacamp.com/tutorial/normalization-in-sql and https://en.wikipedia.org/wiki/Database_normalization.
   
   
3. In PHP Doctrine ORM is the most elaborate framework for ORM using the Data Mapper pattern. It is “transparent” which means that you don’t see any direct connection between your entities and their persistence in the database. In 2013 I gave a presentation about the use of Doctrine ORM in Joomla. In Laravel you have Eloquent as an ORM, based on the Active Record pattern. Here is a short introduction about the differences between Data Mapper and Active Record.

4.  The persistence of the core n:1 and n:n relations are mainly handled in the Table-objects. Joomla’s Table-objects are the entities. They are only used for single objects, not for collections. This is different in Laravel, where the models are the entities. Collections in Laravel are handled by a Collection-object.

   However, in Joomla the 1:1 relationships for the content-related tables for rating and featuring of articles are managed in the article model (featuring in the administrator side and rating in the website side). 

   In Joomla the Table-object is not fully used for all CRUD-activities. It is a bit hybrid division of roles between the Table-object and the model. For the implementation in this article of our event schedule example I first did as much as possible with the Table-object. However, in the future I will do more things in the model; it is simpler as it is more in line with the limited use of the Table-object in Joomla. Akeeba has explored an interesting extended use of the Table-object to represent entities in several extensions, also for handling relations between entities.
   
   For the Joomla Framework an entity package is being developed, with entities (instead of Table-objects). It is an active record implementation with basic ORM-possibilities, inspired on Laravel's Eloquent. It also uses the same names for the relation-objects like HasMany and BelongsTo.
   
   
5. In Joomla core the rating is retrieved in the article model (not via the Table-object) and styled via the vote content-plugin.

Resources

Building a component:

• Joomla! Programmers Documentation, our ever expanding online source of information.
• Book: “Developing Extensions for Joomla! 5” by Carlos Cámara.
• Online book: "Joomla Extension Development"by Nicholas Dionysopoulos.
• Video’s Robbie Jackson. Robbie's videos accompany the documentation. There is also a lot about Joomla 3 extension development that is still useful.
• Look at core components. They are supposed to be an example for your own code.
• Book: "Developing Extensions: Step by step to an working Joomla extension" by Astrid Günther. Out of sale at the moment; hopefully a new version will be published.

Articles in this series about Tools to build a Component:

• Episode 1: Introduction and Core Solution
• Episode 2: An Embedded Application in Joomla
• Episode 3: Creating a Component (this article)

Extra material like the code for our example event schedule component can be found in the repository for this series.