I would like to extend my getListQuery-method. At the moment I choose data from mysql like this:
$query->select(*);
$query->from('`#__hero_invoices` AS a');
// Join over the invoice-data
$query->select("SUM(ip.factor * ip.value) as totalsum");
$query->join("LEFT", "#__hero_invoices_positions AS ip ON ip.invoiceid=a.id");
// Join over the ordermapping-data
$query->select("om.orderid");
$query->join("LEFT", "#__hero_invoices_mapping AS om ON om.invoiceid=a.id");
// Join over the order-data
$query->select("o.userid as userid");
$query->join("LEFT", "#__hero_orders AS o ON o.id=om.orderid");
// Join over the customer-data
$query->select("c.lastname as customer_lastname, c.firstname as customer_firstname, c.company as customer_company");
$query->join("LEFT", "#__hero_customers AS c ON c.userid=o.userid");
// Join over the users for the checked out user
$query->select("uc.name AS editor");
$query->join("LEFT", "#__users AS uc ON uc.id=a.checked_out");
// Join over the user field 'created_by'
$query->select('created_by.name AS created_by');
$query->join('LEFT', '#__users AS created_by ON created_by.id = a.created_by');
Well - now I would like to get information from another model for every result I get AND I would like to still use the sorting-methods.
My first try was to use getListQuery and overwrite getItems() like this:
/**
* getItems function.
*
* @access public
* @return void
*/
public function getItems()
{
$modelinvoice = JModelLegacy::getInstance( 'Invoice', 'HeroModel' );
$items = parent::getItems();
foreach($items as $item)
{
$invoice = $modelinvoice->getItem($item->id);
$item->totalsum = $invoice->totalsum;
}
return $items;
}
This works fine - the view can access "totalsum", but of course I can not use sorting and searching for this pseudo-field using
echo JHtml::_('searchtools.sort', 'COM_HERO_HEADING_TOTALSUM', 'a.totalsum', $listDirn, $listOrder);
in my view.
Any ideas to solve this?