Load a Single Admin view with multiple tables and forms

Question

We have 2 tables that have data and we need to bring data from both the tables and show in one single view.

We have one single XML file which holds the fields of both the tables.

The function 'getForm' in model is able to load all the fields from XML and able to load the HTML elements in the form.

The problem is the function 'getItem' is only able to bring data from one table and not from the other table. I think since the function 'getTable' is tied with the first table it only bring data from the first table.

How can we load data from second table inside getItem?

Answer 1

In your model class you want to override the getItem method like so:

public function getItem() {
    $item = parent::getItem();

    $item->second_table = $this->getSecondTable($item->second_table_foreign_key);
    $item->third_table = $this->getThirdTable($item->third_table_foreign_key);
    return $item;
}

The key here is you still call the parent class getItem as there is a lot of stuff going on in the background, especially if you have any kind of cache enabled. Now, even though you might not have a view, you should set up a model and table class for the secondary table. This way you can easily load the relevant data from you helper methods:

private function getSecondTable($id) {
    $model = JModelLegacy::getInstance('SecondTable','MyComponentModel');
    $table = $model->getTable('SecondTable', 'MyComponentTable');
    return $table->load($id);
}

It is important that your form xml definition field names still match up to the actual column names in the second and third tables. Finally, you will need to do the reverse on the save event. Again, you will overload the save method in the model:

public function save($data) {
    $this->saveSecondTableData($data);
    $this->saveThirdTableData($data);
    parent::save($data);
}

You can use the table class again in your helper methods to save:

private function saveSecondTableData($data) {
    $model = ModelLegacy::getInstance('SecondTable','MyComponentModel');
    $table = $model->getTable('SecondTable', 'MyComponentTable');
    $table->bind($data);
    $table->store();
    parent::save($data);
}

Now, this is an extremely bare bones example here, there's a lot of due diligence I've left out; such as error checking, server side form validation, the table bind, load and store events and displaying alerts, etc.

Also, this assumes you would like to save any changes the ancillary table data to be saved into the DB. If all you want is to display the data a left join in the getListQuery() model method would sort out immediately.

However, the basic concept for connecting multiple tables to a single view is there.