I'm posting this in hopes someone is familiar with the variables and objects and all of that in different sections of JComments.

I use a query in tpl_tree.php to get the date of the last reply made in the thread and that displays on the top level. There may be a better way to write it, but i'm very new to php and databases, and it displays what i need it to correctly.

if ($comment->children == 0) {
echo JCommentsText::formatDate($comment->date, JText::_('D M j Y g:ia')); 
} else { 

   $db = JFactory::getDbo();
   $db->setQuery('SELECT thread_id, date 
                  FROM #__jcomments
                  WHERE thread_id = ' . $comment->id . '
                  ORDER BY date DESC LIMIT 1');
   $db->loadAssocList();

   $row = $db->loadAssocList(); 

   foreach ($row as $item) { 
      echo JHtml::_('date', $item[date], JText::_('D M j Y g:ia')); 
   } 
} ?>

Now the problem seems to be how to get that date to replace the parent = 0 comment date in the order. I can't seem to figure out the syntax to use to perform this query because the ->children doesn't work where the orderby code is set and then have it use that date in place of the parent date when reply cases come up.

The order by is set in jcomments settings in administration, but factoring in replies isn't an option in settings, so code to alter is components/com_jcomments/models/jcomments.php There are 3 references to the order the comments are listed in, the first at the top

if (!isset($options['orderBy'])) {
            $options['orderBy'] = self::_getDefaultOrder();
        }

the second is the case in the query where it sets parent = 0 comments as threaddate:

. "\n,
     case 
       when c.parent = 0 
       then unix_timestamp(c.date)
       else 0 
     end as threaddate"

and the third is the function that feeds the admin setting result to the top code:

if ($config->get('template_view') == 'tree') {
            switch($config->getInt('comments_tree_order')) {
                case 2:
                    $result = 'threadDate DESC, c.date ASC';
                    break;
                case 1:
                    $result = 'c.parent, c.date DESC';
                    break;
                default:
                    $result = 'c.parent, c.date ASC';
                    break;
            }

Case 2 being the orderby the comments are currently set at. I've tried adding cases to the query, but can't seem to find the right wording or syntax to have it look at thread_id and if it matches parent = 0's id then get the most recent date of all of those matches as threaddate to put into the order. Or maybe someone sees a better way to do this extra check in one of the other areas? I've tried pretty much everything i can think of and haven't been able to work it in yet. Any help would be appreciated.

Database sample, database names have been changed and emails are all my testing account emails. https://www.db-fiddle.com/f/4pqz9MFGGaQ1ucB3nmw1De/0

This is the whole query from components/com_jcomments/models/jcomments.php

$query = "SELECT c.id, c.parent, c.object_id, c.object_group, c.userid, c.name, c.username, c.title, c.comment"
        . "\n, c.email, c.homepage, c.date, c.date as datetime, c.ip, c.published, c.deleted, c.checked_out, c.checked_out_time"
        . "\n, c.isgood, c.ispoor"
        . ($votes ? "\n, v.value as voted" : "\n, 1 as voted")
        . "\n, case when c.parent = 0 then unix_timestamp(c.date) else 0 end as threaddate"
        . ($objectinfo ? "\n, jo.title AS object_title, jo.link AS object_link, jo.access AS object_access" : ", '' AS object_title, '' AS object_link, 0 AS object_access, 0 AS object_owner")
        . "\nFROM #__jcomments AS c"
        . ($votes ? "\nLEFT JOIN #__jcomments_votes AS v ON c.id = v.commentid " . ($acl->getUserId() ? " AND  v.userid = " . $acl->getUserId() : " AND v.userid = 0 AND v.ip = '" . $acl->getUserIP() . "'") : "")
        . ($objectinfo ? "\n LEFT JOIN #__jcomments_objects AS jo ON jo.object_id = c.object_id AND jo.object_group = c.object_group AND jo.lang=c.lang" : "")
        . (count($where) ? ("\nWHERE " . implode(' AND ', $where)) : "")
        . "\nORDER BY " . $orderBy
        . (($limit > 0) ? "\nLIMIT $limitStart, $limit" : "");

    return $query;
  • I wish i could, have never done that before and i'm not sure how. Might be some time to figure it out. – Mythic Nov 7 at 4:02
  • It is as easy as going into your phpMyAdmin doing an Export, then dumping the CREATE and INSERT INTO queries into the left side panel, then submit. That will create a url for me to look at. (I'm working at the moment, so I'll have to look after work). meta.stackoverflow.com/a/333953/2943403 – mickmackusa Nov 7 at 4:04
  • imgur.com/a/eJBZ930 Once you have dumped everything into text copy past into sqlfiddle (then remove anything unnecessary / private) then build that schema. That will create the new url for us). – mickmackusa Nov 7 at 4:32
  • Hopefully this is it, sqlfiddle wouldn't load for me db-fiddle.com/f/4pqz9MFGGaQ1ucB3nmw1De/0 – Mythic Nov 7 at 4:50
  • 1
    Yes i think that's got it. You're pretty good at this stuff. Just have to remember to add thread_id to the initial selection above that part of the code as c.thread_id. The alternate order of replies isn't that big of a deal, some already have them set that way. I may mess with that a bit to see if i can get those asc again, but other than that i'm happy to get this far. Thanks again. – Mythic Nov 7 at 23:23
up vote 2 down vote accepted

*Note, that any hacking to the extensions files have the potential to be overwritten when an extension upgrade is performed.

As a pure mysql solution, you only need to write a subquery into the $query @ components/com_jcomments/models/jcomments.php inside of function _getCommentsQuery().

The subquery will effectively assign a parent with the max (latest) date of itself and all of its children based on the thread_id data.

Because the conditional outcome on this condition in the SELECT clause is binary, an IF expression is better suited versus a verbose CASE expression.

. "\n, IF(c.thread_id = 0, (SELECT MAX(date) FROM tsp_jcomments WHERE thread_id = c.id OR id = c.id), c.date) AS threaddate"

UPDATE: After feedback from @Mythic, the desired order dictates that the else value be 0, like this:

"\n, IF(c.parent = 0, (SELECT MAX(date) FROM tsp_jcomments WHERE thread_id = c.id OR id = c.id), 0) AS threaddate

The logic says:

  • if thread_id is 0 (meaning the row is a parent)
    • then find the latest date value from the same table (as a new set of data) so long as the new row's thread_id value is the original row's id value or if the new row's id value is the original row's id.
  • otherwise, (meaning the row is not a parent)
    • use the row's date column value.

Here is a demonstration of the solution on your sample data: https://www.db-fiddle.com/f/4pqz9MFGGaQ1ucB3nmw1De/4

Here is a visual representation of how the assignment works:

||    [logic]             threaddate      | id  | thread_id |       date                
|| ---------------- | ------------------- | --- | --------- | ------------------- 
|| MAX() from 70 -> | 2018-10-18 18:21:58 | 1   | 0         | 2018-07-21 08:04:04 
|| self ->          | 2018-07-24 22:00:26 | 3   | 1         | 2018-07-24 22:00:26 
|| self ->          | 2018-07-24 22:07:59 | 4   | 1         | 2018-07-24 22:07:59 
|| self ->          | 2018-07-29 02:25:02 | 5   | 0         | 2018-07-29 02:25:02 
|| MAX() from 80 -> | 2018-10-20 15:42:41 | 9   | 0         | 2018-07-29 11:43:00 
|| self ->          | 2018-08-07 11:29:05 | 11  | 1         | 2018-08-07 11:29:05 
|| self ->          | 2018-08-19 01:59:46 | 13  | 0         | 2018-08-19 01:59:46 
|| self ->          | 2018-08-20 05:10:22 | 14  | 0         | 2018-08-20 05:10:22 
|| MAX() from 96 -> | 2018-10-28 23:05:59 | 15  | 0         | 2018-09-10 09:16:31 
|| self ->          | 2018-09-10 11:03:08 | 16  | 15        | 2018-09-10 11:03:08 
|| self ->          | 2018-09-10 13:01:59 | 17  | 1         | 2018-09-10 13:01:59 
|| self ->          | 2018-09-12 05:14:56 | 18  | 15        | 2018-09-12 05:14:56 
|| MAX() from 62 -> | 2018-10-10 20:19:25 | 19  | 0         | 2018-09-13 21:43:02 
|| self ->          | 2018-09-13 21:45:25 | 20  | 19        | 2018-09-13 21:45:25 
|| self ->          | 2018-09-13 21:50:27 | 21  | 19        | 2018-09-13 21:50:27 
|| self ->          | 2018-09-13 21:55:03 | 22  | 19        | 2018-09-13 21:55:03 
|| self ->          | 2018-09-13 22:32:04 | 23  | 1         | 2018-09-13 22:32:04 
|| self ->          | 2018-09-14 01:50:59 | 24  | 1         | 2018-09-14 01:50:59 
|| self ->          | 2018-09-14 01:51:53 | 25  | 1         | 2018-09-14 01:51:53 
|| self ->          | 2018-09-14 01:52:42 | 26  | 1         | 2018-09-14 01:52:42 
|| self ->          | 2018-09-14 01:54:22 | 27  | 1         | 2018-09-14 01:54:22 
|| self ->          | 2018-09-14 01:55:36 | 28  | 1         | 2018-09-14 01:55:36 
|| self ->          | 2018-09-14 01:57:33 | 29  | 1         | 2018-09-14 01:57:33 
|| self ->          | 2018-09-14 01:59:30 | 30  | 1         | 2018-09-14 01:59:30 
|| self ->          | 2018-09-16 22:50:29 | 31  | 1         | 2018-09-16 22:50:29 
|| self ->          | 2018-10-07 05:14:01 | 33  | 1         | 2018-10-07 05:14:01 
|| self ->          | 2018-10-07 11:31:46 | 34  | 0         | 2018-10-07 11:31:46 
|| self ->          | 2018-10-07 11:32:44 | 35  | 0         | 2018-10-07 11:32:44 
|| self ->          | 2018-10-07 11:33:17 | 36  | 0         | 2018-10-07 11:33:17 
|| self ->          | 2018-10-07 11:34:06 | 37  | 0         | 2018-10-07 11:34:06 
|| self ->          | 2018-10-07 11:34:41 | 38  | 0         | 2018-10-07 11:34:41 
|| self ->          | 2018-10-07 11:35:21 | 39  | 0         | 2018-10-07 11:35:21 
|| self ->          | 2018-10-07 11:35:59 | 40  | 0         | 2018-10-07 11:35:59 
|| self ->          | 2018-10-07 11:36:44 | 41  | 0         | 2018-10-07 11:36:44 
|| self ->          | 2018-10-09 11:24:22 | 42  | 0         | 2018-10-09 11:24:22 
|| self ->          | 2018-10-09 11:25:03 | 43  | 0         | 2018-10-09 11:25:03 
|| self ->          | 2018-10-09 11:25:41 | 44  | 0         | 2018-10-09 11:25:41 
|| self ->          | 2018-10-09 11:26:42 | 45  | 0         | 2018-10-09 11:26:42 
|| self ->          | 2018-10-09 11:27:29 | 46  | 0         | 2018-10-09 11:27:29 
|| self ->          | 2018-10-09 11:28:07 | 47  | 0         | 2018-10-09 11:28:07 
|| self ->          | 2018-10-09 11:28:58 | 48  | 0         | 2018-10-09 11:28:58 
|| self ->          | 2018-10-09 11:29:44 | 49  | 0         | 2018-10-09 11:29:44 
|| self ->          | 2018-10-09 11:32:02 | 50  | 0         | 2018-10-09 11:32:02 
|| self ->          | 2018-10-09 11:33:34 | 51  | 0         | 2018-10-09 11:33:34 
|| self ->          | 2018-10-09 11:34:10 | 52  | 0         | 2018-10-09 11:34:10 
|| self ->          | 2018-10-09 11:34:58 | 53  | 0         | 2018-10-09 11:34:58 
|| self ->          | 2018-10-09 11:35:57 | 54  | 0         | 2018-10-09 11:35:57 
|| self ->          | 2018-10-09 11:36:28 | 55  | 0         | 2018-10-09 11:36:28 
|| self ->          | 2018-10-09 11:37:30 | 56  | 0         | 2018-10-09 11:37:30 
|| self ->          | 2018-10-09 11:38:01 | 57  | 0         | 2018-10-09 11:38:01 
|| self ->          | 2018-10-09 11:38:43 | 58  | 0         | 2018-10-09 11:38:43 
|| self ->          | 2018-10-09 11:39:17 | 59  | 0         | 2018-10-09 11:39:17 
|| self ->          | 2018-10-10 19:23:52 | 60  | 19        | 2018-10-10 19:23:52 
|| self ->          | 2018-10-10 19:56:09 | 61  | 19        | 2018-10-10 19:56:09 
|| MAX() for 19 ->  | 2018-10-10 20:19:25 | 62  | 19        | 2018-10-10 20:19:25 
|| self ->          | 2018-10-13 21:53:45 | 63  | 0         | 2018-10-13 21:53:45 
|| self ->          | 2018-10-13 21:58:35 | 64  | 0         | 2018-10-13 21:58:35 
|| self ->          | 2018-10-13 22:16:41 | 65  | 0         | 2018-10-13 22:16:41 
|| self ->          | 2018-10-15 05:37:36 | 66  | 1         | 2018-10-15 05:37:36 
|| self ->          | 2018-10-15 06:51:10 | 67  | 1         | 2018-10-15 06:51:10 
|| self ->          | 2018-10-18 18:02:31 | 68  | 1         | 2018-10-18 18:02:31 
|| self ->          | 2018-10-18 18:03:26 | 69  | 1         | 2018-10-18 18:03:26 
|| MAX() for 1 ->   | 2018-10-18 18:21:58 | 70  | 1         | 2018-10-18 18:21:58 
|| self ->          | 2018-10-20 07:46:00 | 71  | 15        | 2018-10-20 07:46:00 
|| self ->          | 2018-10-20 08:16:58 | 72  | 15        | 2018-10-20 08:16:58 
|| self ->          | 2018-10-20 15:01:07 | 73  | 9         | 2018-10-20 15:01:07 
|| self ->          | 2018-10-20 15:04:49 | 74  | 9         | 2018-10-20 15:04:49 
|| self ->          | 2018-10-20 15:05:58 | 75  | 9         | 2018-10-20 15:05:58 
|| self ->          | 2018-10-20 15:40:11 | 76  | 9         | 2018-10-20 15:40:11 
|| self ->          | 2018-10-20 15:40:49 | 77  | 9         | 2018-10-20 15:40:49 
|| self ->          | 2018-10-20 15:41:22 | 78  | 9         | 2018-10-20 15:41:22 
|| self ->          | 2018-10-20 15:42:11 | 79  | 9         | 2018-10-20 15:42:11 
|| MAX() for 9 ->   | 2018-10-20 15:42:41 | 80  | 9         | 2018-10-20 15:42:41 
|| self ->          | 2018-10-21 01:37:22 | 81  | 15        | 2018-10-21 01:37:22 
|| self ->          | 2018-10-22 15:19:45 | 82  | 0         | 2018-10-22 15:19:45 
|| self ->          | 2018-10-23 21:30:41 | 83  | 0         | 2018-10-23 21:30:41 
|| self ->          | 2018-10-25 13:46:26 | 84  | 0         | 2018-10-25 13:46:26 
|| self ->          | 2018-10-25 13:47:13 | 85  | 0         | 2018-10-25 13:47:13 
|| self ->          | 2018-10-25 13:48:03 | 86  | 0         | 2018-10-25 13:48:03 
|| self ->          | 2018-10-25 13:48:37 | 87  | 0         | 2018-10-25 13:48:37 
|| self ->          | 2018-10-25 13:50:01 | 88  | 0         | 2018-10-25 13:50:01 
|| self ->          | 2018-10-25 13:50:31 | 89  | 0         | 2018-10-25 13:50:31 
|| self ->          | 2018-10-25 13:51:18 | 90  | 0         | 2018-10-25 13:51:18 
|| self ->          | 2018-10-25 13:51:50 | 91  | 0         | 2018-10-25 13:51:50 
|| self ->          | 2018-10-25 13:52:23 | 92  | 0         | 2018-10-25 13:52:23 
|| MAX() for 15 ->  | 2018-10-28 23:05:59 | 96  | 15        | 2018-10-28 23:05:59 

This is just one way to solve the issue. I am certain that there could be multiple ways to fix this issue.

p.s. After taking a look under the hood, I don't like some of the coding practices/styling in the extension. It doesn't use Joomla's query building methods, it uses the @ (error-control/stfu operator) which is mostly frowned upon, and the queries themselves could be written more eloquently.

  • I actually got the reply dates to go back to asc order (instead of desc) by adding the case back in if anyone is interested . "\n, case when c.parent = 0 then IF( c.thread_id = 0, (SELECT MAX(date) FROM tsp_jcomments WHERE thread_id = c.id OR id = c.id), unix_timestamp(c.date) ) else 0 end AS threaddate, c.id, c.parent, c.date" – Mythic Nov 8 at 2:12
  • 1
    Because there are no cases (in your sample data) where parent DOES EQUAL 0 and thread_id DOES NOT EQUAL 0 (In other words, when one is 0 so is the other)... I believe you can safely use this simpler condition: IF (c.parent = 0, (SELECT MAX(date) FROM tsp_jcomments WHERE thread_id = c.id OR id = c.id), 0) Please test and confirm my assertion. – mickmackusa Nov 8 at 2:50
  • Yes, does it as well. Simpler. – Mythic Nov 8 at 3:03

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.