Последнюю запись в подчиненой таблице - Разработка расширений для Joomla

$db = JFactory::getDbo();
       $query = $db->getQuery(true);
       $query='SELECT s.*,COUNT(DISTINCT m.message_id) AS count_messages, '
              .' FROM #__subjects AS s'
              .' LEFT JOIN #__messages AS m ON s.subject_id=m.subject_id'
              .' WHERE s.category_id='.(int)$category_id.' AND m.state=1 AND s.state=1' 
              .' GROUP BY s.subject_id';
       $db->setQuery($query);
       $category_subjects = $db->loadAssocList();
       

...
$query='SELECT s.*,COUNT(DISTINCT m.message_id) AS count_messages, 
(SELECT author_id FROM #__messages WHERE subject_id=s.subject_id  ORDER by message_id DESC LIMIT 1) as last_msg_author '
...

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('s.*,COUNT(DISTINCT m.message_id) AS count_messages, m.message_autor');
$query->from('FROM #__subjects AS s');
$query->leftJoin('#__messages AS m ON (s.subject_id=m.subject_id)');
$query->where('s.category_id='.(int)$category_id);
$query->where('m.state=1');
$query->where('s.state=1');
$query->group('s.subject_id');
$query->order('m.message_id ASC');
$db->setQuery($query);
$category_subjects = $db->loadAssocList();

SELECT `t`.*, ...
FROM
(SELECT
.......) AS `t`
JOIN .... ON .............
WHERE ....................;

(SELECT user_id FROM #__esforum_messages ORDER BY message_id  LIMIT 1) AS last_user

(SELECT user_id FROM `#__esforum_messages` ORDER BY message_id DESC LIMIT 1) AS last_user

'(SELECT message_time FROM #__messages WHERE subject_id=s.subject_id ORDER BY message_id DESC LIMIT 1) AS last_time,'
 '(SELECT user_id FROM #__messages WHERE subject_id=s.subject_id ORDER BY message_id DESC LIMIT 1) AS last_user' 

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('s.*,COUNT(DISTINCT m.message_id) AS count_messages, MAX(m.time) AS last_time');
$query->from('FROM #__subjects AS s');
$query->leftJoin('#__messages AS m ON (s.subject_id=m.subject_id)');
$query->where('s.category_id='.(int)$category_id);
$query->where('m.state=1');
$query->where('s.state=1');
$query->group('s.subject_id');
$db->setQuery($query);
$category_subjects = $db->loadAssocList();

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('s.*,COUNT(DISTINCT m.message_id) AS count_messages, SUBSTRING_INDEX(GROUP_CONCAT(m.autor_id ORDER BY m.time DESC), ',', 1) AS last_msg_author');
$query->from('FROM #__subjects AS s');
$query->leftJoin('#__messages AS m ON (s.subject_id=m.subject_id)');
$query->where('s.category_id='.(int)$category_id);
$query->where('m.state=1');
$query->where('s.state=1');
$query->group('s.subject_id');
$db->setQuery($query);
$category_subjects = $db->loadAssocList();