getListQuery: Get values from another method

Question

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?

Answer 1

I assume you have 2 tables and you want to display a list of items from table 1 but add also some data from table 2 (like the total sum) for each item.

If you want to use default Joomla functionality you have to get the total sum in your list query for the first table. This can be done with the help of subselects (MySQL subqueries). You won't be able to use Joomla sort functions if you assign data from the second table via PHP like you currently do.

Your query would look something like this:

SELECT 
    a.field1, 
    a.field2, 
   (SELECT SUM(b.field3) FROM table2 AS b WHERE b.table1_id = a.id) AS totalsum
FROM 
    table1 AS a

The sort field could look like this then:

echo JHtml::_('searchtools.sort', 'COM_HERO_HEADING_TOTALSUM', 'totalsum', $listDirn, $listOrder)

I am happy to edit / improve this answer if you provide more information.