2

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?

  • Have your tried to override getItem method and JOIN other table into first? Just make your own query. – Rene Korss Apr 27 '15 at 7:11
  • Yes. You are correct. We need to join / merge 2 table outputs and return at once – Malaiselvan Apr 27 '15 at 18:00
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.