Selecting a category for the latest comments module

[Opendreas]

Hello. When there are many categories and several languages on the website, the latest comments display module shows new comments are displayed from all categories in all languages, there is no option to select a separate category. For example, you can look at the Joomla new article display module. I don't know if this is technically possible. Thank you.

[nikosdion]

While technically possible, the only way to do it would require doing an inner join with articles and categories tables in \Akeeba\Component\Engage\Administrator\Model\CommentsModel::getListQuery. This has two problems.

Performance. Doing inner joins across three tables requires MySQL to create a memory table with N1 times N2 times N3 rows where N1, N2, and N3 are the number of rows in each of the other tables. Let's say we have a moderately small site with ten thousand comments (N1=10000) across a thousand articles (N2=1000) in ten categories (N3=10). Note that N3 is kept to 10 only because we have a WHERE clause to limit which categories to display. This is still one hundred million rows which means several minutes to run the query on a typical server. This is impractical.

Solving this would require increasing the minimum MySQL version requirement to 8.0.15 so we can use WHERE EXISTS clauses which are far more efficient (I already do that in our private components; queries which required 2+ minutes now only take less than 0.05 seconds!). However, this is above the requirements of Joomla! 4.x. This means that we can only implement this when we drop Joomla! 4.x support which will not be before Joomla! 6 is released in October 2027 – three years from now.

Hard limitation on com_content. The way Engage is written it can be used with any extension which supports asset IDs for its content by writing a content plugin. Adding filtering by category like you want requires doing what I described above, which puts a hard limitation on Engage to only work with com_content. I am not sure I want that, but it's not a deal breaker.

So, for now, the answer is no because of the show-stopper performance issues of using inner joins across big tables.

[Opendreas]

Thanks for the detailed and clear answer. But do I understand correctly that such functionality could theoretically appear in 3 years?

[nikosdion]

Correct.

It is technically possible, as long as we drop Joomla! 4 support OR if we see in our usage stats that the number of Joomla 4.4 sites using MySQL versions lower than 8.0 are not a significant population.

So, sometime in the next 3 years this can happen. When? I cannot tell you right now. Joomla! 4.4 will be going into security-only maintenance mode in a month. I want to see at which rate people migrate their sites, and see which MySQL versions are used in sites not migrated before making any decision.

[Opendreas]

Thank you, I think this topic can be closed.