How to use data from another table in my view?

Question

I'm struggling to learn Joomla 3.x and am baffled as to how to join my record to another table and use the joined data in my view/form. I'm familiar with components that use something similar to this: (I've only included partial snippets because the approach I'm familiar with seems quite common throughout other components and my google results)

models/mymodel.php

// JOIN and retrieve the data I'm interested in:
function getData()
    $query = $db->getQuery(true);
    $query->select('a.name, b.lastvisitDate FROM #__mytable AS a')
          ->innerJoin('#__users AS b ON a.created_by = b.id');

views\myview\tmpl\default.php

// The data from the joined data is available for display something like this
echo "<span>" . $this->item->lastvisitDate. "</span>"

Now, with Joomla 3, I see a series of function such as prepareTable,getTable,getForm,loadFormData, and getItem.

As best as I can tell, it's using predefined forms/XML, fields and such that describe the primary table in great detail. I'm able to change data in getItem that shows on the view, but am clueless about how to get usable data from the second table!

What's the best-practice way to join my primary table to another, and be able to display that information with the view. (e.g. JOIN myTable.created_by on #__users.id to get something like last visit date.)

Answer 1

I'm not sure if this is the best-practice way of doing it (please comment if there's a better way), or it is exactly what you're looking for, but here's my approach using a Joomla article as an example:

Files of Interest

• components\com_content\views\article\view.html.php
• components\com_content\models\article.php

Modify view.html.php

Near the top of public function display($tpl = null), you'll see a line like this:

$this->item = $this->get('Item');

It gets the article data from function getItem located in models\article.php

Duplicate that line to something like this:

$this->item = $this->get('Mydata');

Modify article.php

Now you have to add the function to get the data you're interested in with this block of code: (named according to how you're calling it, preceded by 'get')

 public function getMydata($pk = null)
{
    $myItemObj = new stdClass();
    $myItemObj->something = 'something you need';

    $db    = JFactory::getDbo();
    $query = $db->getQuery(true);
    $query->select('*')
    ->from($db->quoteName('#__users'))
    ->where($db->quoteName('id') . ' = 467');
    $db->setQuery($query);
    $myItemObj->MydbObj = $db->loadObject();

    return $myItemObj;
}

In your view, you can now access that data like this:

echo "<span>". $this->myData->something ."</span>";
echo "<span>". $this->myData->MydbObj->username ."</span>";

You may want to check Using multiple models in an MVC component if you need a more comprehensive solution.