2

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?

  • From which model is the getItems() method you're overwriting? That is the model where you have to change the getListQuery method to get the results for totalsum – fruppel Sep 1 '15 at 15:21
  • Hi, I already GET the totalsum - but I can not sort my table by totalsum - and I can not search for values. – MyFault Sep 1 '15 at 17:06
  • You did not answer my question. Are you overwriting the getItems method from your HeroModelInvoice class? – fruppel Sep 1 '15 at 18:48
3

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.

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.