Model should load data from another table and create associative array

Question

I would like to extend my learning-component that I'm writing. For this reason I'm creating a little ticketing-system which fetches mail from IMAP and saves attachments if they have a specific filetype (only png, jpg, ...) .

While saving the attachments the filename is hashed and saved afterwards.

Of course I would like to extend my ticket-view aswell: In my ticket-view there are all messages assigned to this ticket (used models: ticket, messages).

Now I would like to show downloadable attachments for each message in this ticket which has one or more attachments. Clicking "download attachment" should start download-process.

I tried to extend messages-model with left join but it does not work that good.

This is my code (model):

<?php

    defined('_JEXEC') or die;

    class BestiaModelMessages extends JModelList
    {

        /**
         * __construct function.
         * 
         * @access public
         * @param array $config (default: array())
         * @return void
         */
        public function __construct($config = array())
        {

            if (empty($config['filter_fields']))
            {
                $config['filter_fields'] = array(
                                                'id', 'a.id',
                                                'ticketid' , 'a.ticketid',
                                                'from', 'a.from',
                                                'to', 'a.to',
                                                'date', 'a.date',
                                                'content', 'a.content',
                                                'title', 'a.title'
                                                ); 
            }   

            parent::__construct($config);
        }

        /**
         * populateState function.
         * 
         * @access protected
         * @param mixed $ordering (default: null)
         * @param mixed $direction (default: null)
         * @return void
         */
        protected function populateState($ordering = null, $direction = null)
        {
            $published = $this->getUserStateFromRequest($this->context.'.filter.state', 'filter_state', '', 'string');
            $this->setState('filter.state', $published);
            parent::populateState('a.id', 'desc');      
        }

        /**
         * getListQuery function.
         * 
         * @access protected
         * @return void
         */
        protected function getListQuery()
        {
            $db    = $this->getDbo();
            $query  = $db->getQuery(true);
            $query->select(
            $this->getState(
                            'list.select',
                            'a.id, a.ticketid, a.from, a.to, a.date, a.title, a.content'));

            $query->from($db->quoteName('#__bestia_tickets_messages').' AS a');

            // Filter by search in title
            $search = $this->getState('filter.search');

            if (!empty($search))
            {

                if (stripos($search, 'id:') === 0)
                {
                    $query->where('a.id = ' . (int) substr($search, 3));
                }

                else
                {
                    $search = $db->quote('%' . $db->escape($search, true) . '%');
                    $query->where('(a.content LIKE ' . $search.' OR a.id LIKE ' . $search . 'OR a.from LIKE ' . $search . 'OR a.to LIKE ' . $search . 'OR a.date LIKE ' . $search .' OR a.title LIKE ' . $search . ')');
                }

            }           


            $query->select('GROUP_CONCAT(cast(concat(tma.filename,\', \',tma.filepath)AS char) SEPARATOR \', \') AS attachments')
                  ->join('LEFT', '#__bestia_tickets_messages_attachments as tma ON a.id = tma.messageid');



            $model =& $this->getInstance('Ticket', 'BestiaModel');          
            $item = $model->getItem();
            $ticketid = $item->ticketid;

            $query->where('a.ticketid = '.$db->quote($ticketid));
            $query->order('a.date DESC');  
            $query->group('a.id');

//          var_dump((string)$query);

           return $query;
         } // ./protected function getListQuery

    } // ./class BestiaModelMessages

The resulting output looks like this:

object(stdClass)#375 (8) { 
    ["id"]=> string(3) "725" 
    ["ticketid"]=> string(11) "123456789" 
    ["from"]=> string(16) "[email protected]" 
    ["to"]=> string(27) "[email protected]" 
    ["date"]=> string(19) "2015-08-03 14:47:27" 
    ["title"]=> string(17) "Ticket mit Anhang" 
    ["content"]=> string(775) "Ticket mit Anhang…" 
    ["attachments"]=> string(217) "Test.png, asdaadasasgsdafasdasdasgasfasf, Bildschirmfoto 2015-07-14 um 17.10.43.png, 52d0833774bc94698e4101c021deebecf5724a4c65d1efc477948180c072c61778b6c0174040cdef6d94d8669251f0a6646c5caf6e1966b9796625979973b2a4.png" 
}

Now I would like to associate the filename and the file path to use it like this:

foreach($message->attachments as $attachment)
{
     $attachment->filename;
     $attachment->filepath;
}

How could I solve this problem?

Answer 1

I don't see where in your code you are outputting the object that you mentioned, normally the query produced by getListQuery should result in an array. I'm presuming that object is an example of one that might be contained within the array?

If so then I would replace your group concat as follows

$query->select('GROUP_CONCAT(cast(concat(tma.filename,\'|\',tma.filepath)AS char) SEPARATOR \',\') AS attachments')
              ->join('LEFT', '#__bestia_tickets_messages_attachments as tma ON a.id = tma.messageid');

that would give you something like

["attachments"]=> string(217) "Test.png|asdaadasasgsdafasdasdasgasfasf, Bildschirmfoto 2015-07-14 um 17.10.43.png|52d0833774bc94698e4101c021deebecf5724a4c65d1efc477948180c072c61778b6c0174040cdef6d94d8669251f0a6646c5caf6e1966b9796625979973b2a4.png" 

So say you have elsewhere:

$query = $this->getListQuery();
$rows = $db->setQuery($query)->loadObjectList();

you can now iterate over those rows as follows:

foreach ($rows as &$row)
{
 $attachments = explode(',' $row->attachments);
 $res = array();

  foreach ($attachments as $attachment) 
  {
    $attachment = explode('|', $attachment);
    $obj = new stdClass; 
    $obj->filename = $attachment[0];
    $obj->filepath = $attachment[1];
    $res[] = $obj;
  }

  $row->attachments = $res;
}

To be on the safe side I would also replace the two concat separators '|' and ',' with a string you are sure won't be present in the filename/path. E.g in fabrik we use something like '//..//'

As an aside I'm not sure why you are loading the tickets model with the getListQuery as well as it appears not to be used?

An alternative method would be to dump the group concat altogether and to do it as two separate queries: Lets say you have got the list of tickets as $tickets (an array of objects).

use Joomla\Utilities\ArrayHelper;
$ids = ArrayHelper::getColumn($tickets, 'id');
$ids = ArrayHelper::toInteger($ids);

if (!empty($ids)) 
{
  $query = $db->getQuery(true);
  $query->select('filename, filepath')
  ->from('#__bestia_tickets_messages_attachments')
 ->where('ticket_id IN (' . implode(',', $ids) . ')');
  $db->setQuery($query);
  $attachments = $db->loadObjectList('ticket_id');
}

foreach ($tickets as &$ticket) 
{
  $ticket->attachments = ArrayHelper::getValue($attachments, $ticket->id, array());
}

This may be marginally slower, but is more readable.

Answer 2

GROUP_CONCAT is a performance killer. Not to mention that in your query it is possible that it will join rows before it will filter them. So will waste a lot of memory and cpu time on bigger data sets.

I would remove this:

$query->select('GROUP_CONCAT(cast(concat(tma.filename,\', \',tma.filepath)AS char) SEPARATOR \', \') AS attachments')
              ->join('LEFT', '#__bestia_tickets_messages_attachments as tma ON a.id = tma.messageid');

Create function to bind attachments:

protected function bindAttachments(Array &$items) {
    // Get messages ids
    $ids = array();
    foreach( $items AS $item ) {
        $ids[] = (int)$item->id;
    }

    // Get attachments
    $query = $this->_db->getQuery(true);
    $query->select('*');
    $query->from('#__bestia_tickets_messages_attachments');
    $query->where('messageid IN('.implode(',', $ids).')');
    $query->order('messageid ASC');

    $this->_db->setQuery($query);
    $attachments = $this->_db->loadObjectList();

    // Create attachments map by ID
    $attachments_map = array();
    foreach( $attachments AS $attachment ) {
        if( !isset($attachments_map[$attachment->messageid]) ) {
            $attachments_map[$attachment->messageid] = array();
        }

        $attachments_map[$attachment->messageid][] = $attachment;
    } 

    // Attach attachments to items array
    foreach( $items AS &$item ) {
        if( isset($attachments_map[$item->id]) ) {
            $item->attachments = $attachments_map[$item->id];
        } else {
            $item->attachments = array();
        }
    }
}

And now just override your getItems function like this:

public function getItems() {
    $items = parent::getItems();
    $this->bindAttachments($items);

    return $items;
}