Similar tags module articles ordering by date

Question

My joomla version is 3.7.5.
I'm using the similar tags module for dispaying related articles in article view.
Options in module manager are matching tags "all", "half", and "random". I'm using the "all" option.

It works fine but it's ordering the article list in ascending order (e.x. in category auto shows, it's displaying articles, first from 2012, then from 2013 etc).

I want to change the ordering to descending (by published date). The code in similar tags helper file is:

// Optionally filter on language
    $language = JComponentHelper::getParams('com_tags')->get('tag_list_language_filter', 'all');

    if ($language !== 'all')
    {
        if ($language === 'current_language')
        {
            $language = JHelperContent::getCurrentLanguage();
        }

        $query->where($db->quoteName('cc.core_language') . ' IN (' . $db->quote($language) . ', ' . $db->quote('*') . ')');
    }

    $query->group(
        $db->quoteName(
            array('m.core_content_id', 'm.content_item_id', 'm.type_alias', 'ct.router', 'cc.core_title',
            'cc.core_alias', 'cc.core_catid', 'cc.core_language', 'cc.core_params')
        )
    );

    if ($matchtype === 'all' && $tagCount > 0)
    {
        $query->having('COUNT( ' . $db->quoteName('tag_id') . ')  = ' . $tagCount);
    }
    elseif ($matchtype === 'half' && $tagCount > 0)
    {
        $tagCountHalf = ceil($tagCount / 2);
        $query->having('COUNT( ' . $db->quoteName('tag_id') . ')  >= ' . $tagCountHalf);
    }

    if ($ordering === 'count' || $ordering === 'countrandom')
    {
        $query->order($db->quoteName('count') . ' DESC');
    }

    if ($ordering === 'random' || $ordering === 'countrandom')
    {
        $query->order($query->Rand());
    }

    $db->setQuery($query, 0, $maximum);

What change can i do to change the order?

Answer 1

Though you have found your own resolution, I would like to blow my whistle on a few points and offer some suggestions that are too wordy to squeeze into a comment.

Suggested Code:

$language = JComponentHelper::getParams('com_tags')->get('tag_list_language_filter', 'all');

if ($language !== 'all') {
    if ($language === 'current_language') {
        $language = JHelperContent::getCurrentLanguage();
    }
    $query->where("cc.core_language IN (" . $db->q($language) . ", '*')");
}

$query->group(
    array('m.core_content_id', 'm.content_item_id', 'm.type_alias', 'ct.router', 'cc.core_title', 'cc.core_alias', 'cc.core_catid', 'cc.core_language', 'cc.core_params')
);  // ^-- this many columns is questionable

if ($tagCount) {
    if ($matchtype === 'all') {
        $query->having("COUNT(tag_id) = $tagCount");
    } elseif ($matchtype === 'half') {
        $query->having("COUNT(tag_id) >= " . ceil($tagCount / 2));  // removed single-use variable
    }
}

// I _think_ this is your intended logic...

if ($ordering === 'random' || $ordering === 'countrandom') {
    $query->order("RAND()");
}

if ($ordering === 'count' || $ordering === 'countrandom') {
    $query->order("COUNT(tag_id) DESC");  // You are counting the `tag_id`s right? Wise database architects generally don't name a column `count` because it is a keyword.
}

$query->order("m.content_item_id DESC");  // this is the final tie-breaker

$db->setQuery($query, 0, $maximum);

• None of your table names/aliases or column names contain spaces, nor are they MYSQL RESERVED keywords, so you can safely omit those $db->quoteName() calls. If you want to shorten your lines of code, you can use the shorthand alias: $db->qn(). $db->quote() (or $db->q()) is a wise choice on variables that might not be numeric -- so those should stay for security reasons.
• Your group() method seems excessively specific about the grouping terms. While I am not familiar with the Similar Tags module, it seems unlikely that you need to group by all of those columns to identify uniqueness (I could be wrong). My advice is to minimize the number of columns in your group() method so that the database isn't performing unnecessarily.
• Since $tagCount is sure to be a non-negative integer, the conditional syntax can be simplified [see my snippet]. To eliminate redundant conditional checks, nesting your $matchtype conditions will be more direct.
• $query->rand() is not a Joomla MySQL method (and if it was, you should be pulling it from $db), so I ran a test and ->dump()ed the query to see what it generated. To my surprise, it wrote RAND() into the query as intended. If you try to write: $querySelect->rand();, nothing is added to the query (probably because it's not real). For brevity's sake and to avoid teaching Joomla researchers bad habits, I recommend that you omit the erroneous method call and just write "RAND()" as your ->order() parameter.
• When you say:

  I added $query->order($db->quoteName('m.content_item_id') . ' DESC'); under if ($matchtype === 'all' && $tagCount > 0) { $query->having('COUNT( ' . $db->quoteName('tag_id') . ') = ' . $tagCount); and it does the trick.

  I have concerns. If you write m.content_item_id DESC as the first column to sort by, then count, and finally RAND() ...then the logic says that you only consider the count values when there is a duplicate value in m.content_item_id (I'm not even convinced that the ambiguous column name "count" even exists, then again I don't know which tables you are referencing with these aliases). Furthermore, your third condition in the expression "RAND()" will only come into play if there is tie on both of the previously mentioned column values. It just doesn't seem like sound logic. If you want a random selection, I think that condition needs to come first or second at the latest. For future researchers, I hope that you will clarify your coding intent.

Answer 2

After some testing, I found the solution. I added

$query->order($db->quoteName('m.content_item_id') . ' DESC');

underneath the following code

if ($matchtype === 'all' && $tagCount > 0) { $query->having('COUNT( ' . $db->quoteName('tag_id') . ') = ' . $tagCount);

and it does the trick. Unfortunately, like all core hacks, with every Joomla update, I must change the file again (can't use template override for helper.php files). Then I found MVC override plugin from Joomla documentation and I made a template override for mod_tags_similar/helper.php file. It works fine.